Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Unknown NULLs Expand / Collapse
Author
Message
Posted Wednesday, August 12, 2009 9:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 11:42 AM
Points: 43, Visits: 46
Comments posted to this topic are about the item Unknown NULLs
Post #769840
Posted Thursday, August 13, 2009 1:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:01 AM
Points: 1,539, Visits: 8,133
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
Blog Site
Post #769936
Posted Thursday, August 13, 2009 1:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:02 AM
Points: 1,105, Visits: 298
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
Posted Thursday, August 13, 2009 5:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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
Posted Thursday, August 13, 2009 8:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:51 PM
Points: 2,607, Visits: 17,910
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
Posted Thursday, August 13, 2009 9:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:20 PM
Points: 33,078, Visits: 15,192
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
Posted Thursday, August 13, 2009 11:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 24, 2014 6:59 AM
Points: 90, Visits: 290
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
Posted Thursday, August 13, 2009 11:50 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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
Posted Thursday, August 13, 2009 11:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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
Posted Friday, August 14, 2009 2:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:02 AM
Points: 1,105, Visits: 298
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse