Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unknown NULLs


Unknown NULLs

Author
Message
Condorman
Condorman
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 51
Comments posted to this topic are about the item Unknown NULLs
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 11203
An entertaining question, but the formatting codes are all over it, making it difficult to read. I've seen this a few times now, so assume its a problem with posting the questions.

Steve Hall
Linkedin
Blog Site
Kevin Gill
Kevin Gill
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1127 Visits: 356
True - quite difficult to extract the actual content. Spose we could have waited for it on email...

I mistakenly interpreted 'not @i = 1' as being the same as '@i <> 0' - too early in the morning for me I think...

-------------------------------
Oh no!
kramaswamy
kramaswamy
SSC Eights!
SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)

Group: General Forum Members
Points: 859 Visits: 1788
this was like, an entire lecture's worth of material in the database course i just finished taking Tongue made it a bit easier to figure out, lol
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18651
I believe SSC changed the code tags awhile back - I wonder if it happens to be that the question was submitted before the change and published afterwards.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36172 Visits: 18751
Yep, code tags changed. formatting corrected.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SkyMac
SkyMac
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 366
hi, i'm new with sql server, i answered A and i got a "wrong answer". The thing is, you are using a "@i is null" not a "@i = NULL". I've read the following article:

http://msdn.microsoft.com/en-us/library/aa259229(SQL.80).aspx

and in one part it says: "For a script to work as intended, regardless of the ANSI nulls database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that may contain null values."

so, the answer would be always A 'cos you're using a "is null" not a " = NULL"

am i wrong? maybe i didn't understand the article.

thanks!
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
So what's the correct method ? ANSI_NULLS should be ON or OFF ? By default I set it to ON on all distributed queries and when creating store procedures and manipulating indexes.

SET ANSI_NULLS ON
i is null
i <> 0
i = 0

SET ANSI_NULLS OFF
i is null
i <> 0
i <> 0

SQL DBA.
kramaswamy
kramaswamy
SSC Eights!
SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)

Group: General Forum Members
Points: 859 Visits: 1788
i think you just misinterpreted the answers. when he says "i is null", he means that the result of "If @i is null" evaluates to true, and so he's just printing "i is null".

to break down the question,

"Declare @i int"

@i is declared as an int, and not given an initial value. as such, when initialized, it defaults to a value of NULL.

"--Test #1
If @i is null
Print 'i is null'
Else
Print 'i is not null'"

test 1 is checking to see if the value of @i is null. in this case, since it was defaulted to null, test 1 evaluates to true. on the true case, it prints "i is null", so that's what is displayed.

"--Test #2
if @i = 0
Print 'i = 0'
Else
Print 'i <> 0'"

test 2 is checking to see if the value of @i is 0. since @i is null, it fails this check, as @i = 0 evaluates to "UNKNOWN" due to @i being null. so, the else case is used.

"--Test #3
If not @i = 0
Print 'i <> 0'
Else
Print 'i = 0'"

test 3 is checking to see if the value of @i is not 0. again, since @i evaluates to null, and null is not equal to zero, the statement returns false. and not false is true, so the first case is used, and "i <> 0" is printed.

ANSI_NULLS causes the script to return UNKNOWN when comparing any result against NULL.

as for your question Sanjay, i'd suggest that you always use ANSI_NULLS on, so that you can avoid running into any problems when doing null comparisons.
Kevin Gill
Kevin Gill
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1127 Visits: 356
Indeed. ANSI NULLS disabling is just a workaround I think for backwards compatibility with something.

Essentially an 'IF A = B' statement is saying that is (A = B) returns true, then do whatever the IF suggests. so...

With ANSI NULLS ON :

NULL = NULL returns UNKNOWN so 'IF NULL = NULL will go down the ELSE route
NULL <> NULL returns UNKNOWN so 'IF NULL <> NULL will go down the ELSE route
NULL IS NULL returns TRUE so 'IF NULL IS NULL will go down the IF route
NULL IS NOT NULL returns FALSE so 'IF NULL IS NOT NULL will go down the ELSE route

The difference with ANSI NULLS OFF is simply that the = operator and the 'IS' operator are treated the same. Instead of being treated as an unspecified value, NULL is treated as a 'value of NULL' if you see what I mean. Therefore

With ANSI NULLS OFF :

NULL = NULL returns TRUE so 'IF NULL = NULL will go down the IF route
NULL <> NULL returns FALSE so 'IF NULL <> NULL will go down the ELSE route
NULL IS NULL returns TRUE so 'IF NULL IS NULL will go down the IF route
NULL IS NOT NULL returns FALSE so 'IF NULL IS NOT NULL will go down the ELSE route

The main impact of this that I've come across is that if you have two variables and both are null, the only complete equality check with ANSI NULLS on is :

IF @a = @b OR (@a IS NULL AND @b IS NULL)
or shortened using my favourite TSQL function, to

IF COALESCE(@a,-1) = COALESCE(@b,-1)

Check the execution plan though, sometimes the longhand approach is considered more efficient if performance is important.

-- Kev

-------------------------------
Oh no!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search