Unknown NULLs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mike Molnar » Unknown NULLs

21 posts, Page 1 of 3

1 2 3 »»»

Unknown NULLs

Rate Topic

Display Mode

Topic Options

Author

Message

Condorman

Posted Wednesday, August 12, 2009 9:50 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 12:11 PM
Points: 43, Visits: 37

Comments posted to this topic are about the item Unknown NULLs

Post #769840

BrainDonor

Posted Thursday, August 13, 2009 1:42 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, June 14, 2013 8:33 AM
Points: 1,402, Visits: 6,950

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.

BrainDonor
Linkedin

Post #769936

Kevin Gill

Posted Thursday, August 13, 2009 1:45 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, June 07, 2013 4:08 AM
Points: 941, Visits: 239

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!

Post #769940

kramaswamy

Posted Thursday, August 13, 2009 5:34 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:49 PM
Points: 785, Visits: 1,536

this was like, an entire lecture's worth of material in the database course i just finished taking :P made it a bit easier to figure out, lol

Post #770057

Chad Crawford

Posted Thursday, August 13, 2009 8:44 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:31 PM
Points: 2,558, Visits: 17,421

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.

Post #770212

Steve Jones - SSC Editor

Posted Thursday, August 13, 2009 9:36 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:19 AM
Points: 31,527, Visits: 13,865

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

Post #770277

Edward01

Posted Thursday, August 13, 2009 11:38 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 6:17 AM
Points: 34, Visits: 155

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!

Post #770368

SanjayAttray

Posted Thursday, August 13, 2009 11:50 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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.

Post #770376

kramaswamy

Posted Thursday, August 13, 2009 11:57 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:49 PM
Points: 785, Visits: 1,536

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.

Post #770384

Kevin Gill

Posted Friday, August 14, 2009 2:25 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, June 07, 2013 4:08 AM
Points: 941, Visits: 239

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!

Post #770743

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions