asked
Insert Zero instead of Null
HI I had a table which had around 10 columns with INT data type Inserts in this table are happening from multiple locations so sometimes some number are inserted in 10 Int columns and some time Null values , These Table is getting used to show summary data at many pages So my question here is is there any way where I can store Zero instead of saving null and I do this at one place like adding a constraint at column rather than changing all Insertion sp or Displaying Sp
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
I reckon that statistically, zero & null are different values - Null in a sales column, for example, would imply that an item wasn't actually available during that period, where a zero would indicate that it was available, but no-one was buying. As for how to do your null-to-zero conversion... Given that you've ruled out changing the insertion procedures, and changing the display procedures (which would allow us to use a view or computed columns to allow you to keep the original data but display based on the altered data), that really only leaves one option - change the data either as it is added, or when the job is done. So - changing when the job is done - easiest option, with an UPDATE; or, more likely, ten updates. But you knew this. The fun one is changing the data as it's input - in which case a [`TRIGGER`][1] is the way to go... [1]:
https://msdn.microsoft.com/en-us/library/ms189799.aspx
Use DEFAULT constraint (= 0) on columns.
17 People are following this question.
