question

Bhupendra99 avatar image
Bhupendra99 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
nullvalues
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sule avatar image
Sule answered
Use DEFAULT constraint (= 0) on columns.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.