int column gets value of 0 by default

Question

int column gets value of 0 by default

wendy elizabeth

SSCoach

Points: 16915
More actions
February 13, 2013 at 7:59 am

#274126

In a sql server 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?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

I'd blame the application that might be inserting rows into the table; chances are the code is inserting a default of zero from the application, which is outside of the database's control.

for example, i might have code that does this:

DataTable.Rows(0)!MAIN = GetInteger(SomeObject)

'instead of

DataTable.Rows(0)!MAIN = IIF(GetInteger(SomeObject)=0,DbNull.Value,GetInteger(SomeObject))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Are you sure that there is no default on the column?

sp_help [yourtablename] to see ones.

How your application is talking to DB, are you using N-Hibernate by any chance?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ScottPletcher SSC Guru Points: 101200 More actions · Answer 3

Maybe an empty value/string of '' is somehow getting passed to that column? An int of '' is set to 0.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 4

On the SQL side if you want any zeroes to be null you do this:

SELECT NULLIF(fooval,0) AS foonull FROM foo

If you need to validate all values you can do something like this:

SELECT

(CASE

WHEN ISNUMERIC(NULLIF(NULLIF(fooval,''),0)) = 1 THEN fooval

ELSE NULL

END) AS foonull

FROM foo