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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 int column gets value of zero

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 = 0
DROP TABLE #tmp
Go to Top of Page

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] NULL

there is no default value set for this column
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -