SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unknown NULLs


Unknown NULLs

Author
Message
Condorman
Condorman
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 51
Comments posted to this topic are about the item Unknown NULLs
Steve Hall
Steve Hall
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7957 Visits: 12076
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1559 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
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7621 Visits: 1861
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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6033 Visits: 18732
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 Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: Administrators
Points: 250030 Visits: 19813
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 Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 396
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
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7257 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
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7621 Visits: 1861
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1559 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