Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jassie
Constraint Violating Yak Guru
332 Posts |
Posted - 2013-02-13 : 09:56:09
|
In a sql seerver 2008 r2 database, there is a column called main that is declared as an integer on a production database. This column is setup to be null and values are placed in this column by .net programs. However, I am finding zeroes in this column and I have no explaination for it. Thus can you tell me if a column that is declared as an interger and allows null on a production database that zeroes will be placed into the field somehow by default? If the above is true, can you tell me and/or point me to a reference that will explain to me how this is possible? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-13 : 10:12:51
|
A column being NULLable does not mean that it cannot store zeros.So the likely possibility is that the .Net program (perhaps inadvertently inserted zeros into that column). Another possibility is if the column has a default value. See id2 column in the example below. You can script the table from SSMS object explorer to see if a default value has been set up for that column.CREATE TABLE #tmp (id1 INT, id2 INT NULL DEFAULT 0); INSERT INTO #tmp (id1) VALUES (2);SELECT * FROM #tmp; -- shows id1 = 2, id2 = 0DROP TABLE #tmp |
|
|
jassie
Constraint Violating Yak Guru
332 Posts |
Posted - 2013-02-13 : 10:30:24
|
When I did a script on the table, the column looks like the following: [main] [int] NULLthere is no default value set for this column |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-13 : 10:33:59
|
In that case, it must have come from an insert or update, either from the .Net client, or perhaps from a trigger or other stored procedure that indirectly modified it. I would start by looking at the .Net clients if in fact they are sending a 0 or null. Unless they are using C# nullable int ( CLR System.Nullable<int>) to populate their parameters, C#/.Net will send a zero to the server. |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-02-13 : 11:54:39
|
When you script a database the default constrants are after the create as alter table commands.djj |
|
|
|
|
|