SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 int column gets value of zero		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Posting Yak Master

114 Posts
Posted - 02/13/2013 :  09:56:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1483 Posts
Posted - 02/13/2013 :  10:12:51  Show Profile  Reply with Quote
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
Posting Yak Master

114 Posts
Posted - 02/13/2013 :  10:30:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1483 Posts
Posted - 02/13/2013 :  10:33:59  Show Profile  Reply with Quote
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
Yak Posting Veteran

USA
84 Posts
Posted - 02/13/2013 :  11:54:39  Show Profile  Reply with Quote
When you script a database the default constrants are after the create as alter table commands.

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000