August 12, 2009 at 9:50 pm
Comments posted to this topic are about the item Unknown NULLs
August 13, 2009 at 1:45 am
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!
August 13, 2009 at 5:34 am
this was like, an entire lecture's worth of material in the database course i just finished taking 😛 made it a bit easier to figure out, lol
August 13, 2009 at 8:44 am
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.
August 13, 2009 at 9:36 am
Yep, code tags changed. formatting corrected.
August 13, 2009 at 11:38 am
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!
August 13, 2009 at 11:50 am
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.
August 13, 2009 at 11:57 am
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.
August 14, 2009 at 2:25 am
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-2 OR (@a IS NULL AND @b-2 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!
August 14, 2009 at 12:28 pm
kramaswamy (8/13/2009)
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.
Wait a minute! Doesn't that mean the answer is "i is null" followed by two "i 0" statements? Which would then mean the answer should be B if ANSI_NULLS is set on.
I'm confused, now.
August 14, 2009 at 12:46 pm
Actually, you're right - I was wrong in what I wrote earlier.
When you have "IF NOT @i = 0", that evaluates into "IF NOT (UNKNOWN)", which results in FALSE.
IF (UNKNOWN) and IF NOT (UNKNOWN) both evaluate to false, because UNKNOWN is neither true nor false. that is - if ANSI NULLS are on.
August 14, 2009 at 3:24 pm
kramaswamy (8/14/2009)
Actually, you're right - I was wrong in what I wrote earlier.When you have "IF NOT @i = 0", that evaluates into "IF NOT (UNKNOWN)", which results in FALSE.
IF (UNKNOWN) and IF NOT (UNKNOWN) both evaluate to false, because UNKNOWN is neither true nor false. that is - if ANSI NULLS are on.
Thanks for clarifying.
August 25, 2009 at 1:49 pm
Glad to see this get covered. I know this tripped me up early on in the process of learning SQL..
A more experienced person (our DBA) explained it to me thusly
"null is never equal to, less than, greater than, or to anything." "ANY attempt to compare NULL with anything else, will fail"
in other words, pardon the pun but, 'null is beyond compare.'
This was followed by "if you need to do such a comparison in a case where some of the 'values' may be null, then learn to use ISNULL"
ISNULL is your friend!
August 25, 2009 at 1:57 pm
SQAPro (8/25/2009)
Glad to see this get covered. I know this tripped me up early on in the process of learning SQL..A more experienced person (our DBA) explained it to me thusly
"null is never equal to, less than, greater than, or to anything." "ANY attempt to compare NULL with anything else, will fail"
in other words, pardon the pun but, 'null is beyond compare.'
This was followed by "if you need to do such a comparison in a case where some of the 'values' may be null, then learn to use ISNULL"
ISNULL is your friend!
'Course that's dependent upon ANSI_NULLS 😛
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply