SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Mike Molnar / Unknown NULLs / Latest Posts

RE: Unknown NULLs

L' Eomot Inversé — Fri, 23 Mar 2012 17:03:22 GMT

[quote][b]Kevin Gill (10/28/2009)[/b][hr]My example was just a simplified version where you might be comparing foreign keys which map to identity columns and thus can never validly be negative.[/quote]Identity columns can easy be negative. Create them with a negative seed, or with a negative increment, or both is one way. setting identity insert on and placing explicit negative values is another. They are quite common when a large range is needed (bigger than 2 billion) - an identity column starting with seed 2**-31 and increment +1 gives the maximum possible range of values.[quote]Saying 'it isn't something you should use' seems a little black and white when it's perfectly valid in many situations...[/quote]OK, maybe it should have been "it isn't something you should use unless you are absolutely certain you can pick a null-replacement value that is guaranteed to be safe". But remember that Murphy's law applies, so that most people will sometimes choose an "absolutely guaranteed safe" value that isn't safe at all.

RE: Unknown NULLs

IgorMi — Fri, 23 Mar 2012 15:54:29 GMT

I run your question with SET ANSI_NULLS ON and SET ANSI_NULLS OFF, and the result is same. I use SQL Server 2012?hm?

RE: Unknown NULLs

Kevin Gill — Wed, 28 Oct 2009 03:54:09 GMT

[quote][b]Tom.Thomson (10/27/2009)[/b][hr][quote][b]Kevin Gill (8/14/2009)[/b][hr]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)-- Kev[/quote]Although the version with coalesce does work when both @a and @b are null, it will also deliver TRUE is one of them is null and the other is -1; so it isn't something you should use.[/quote]Sorry I was considering it as a given that you do not coalesce them to anything that they can possibly be - for example if it's a quantity then negative numbers are safe, if it's a date then the beginning or end of time would generally be safe. My example was just a simplified version where you might be comparing foreign keys which map to identity columns and thus can never validly be negative.Saying 'it isn't something you should use' seems a little black and white when it's perfectly valid in many situations...

RE: Unknown NULLs

L' Eomot Inversé — Tue, 27 Oct 2009 09:01:52 GMT

[quote][b]Kevin Gill (8/14/2009)[/b][hr]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)-- Kev[/quote]Although the version with coalesce does work when both @a and @b are null, it will also deliver TRUE is one of them is null and the other is -1; so it isn't something you should use.

RE: Unknown NULLs

Kevin Gill — Wed, 26 Aug 2009 01:42:57 GMT

[quote][b]SQAPro (8/25/2009)[/b]...snip...ISNULL is your friend![/quote]Or the ANSI standard COALESCE for the ANSI pedants among us :o) Has the added advantage of being able to take more than two operands. It will simply return the first non-null one. I believe ISNULL is limited to two.declare @i char(1), @j varchar(1)SELECT COALESCE(@i, @j, 'Both Missing')

RE: Unknown NULLs

SQAPro — Tue, 25 Aug 2009 14:20:02 GMT

[quote][b]kramaswamy (8/25/2009)[/b][hr][quote][b]SQAPro (8/25/2009)[/b][hr]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![/quote]'Course that's dependent upon ANSI_NULLS :P[/quote]And since you might not always know or be in control of the ANSI_NULLS setting, then better to just use isnull. That also allows you to control how you treat a null, or substitute some standard value for it. (presuming you don't use 'is null' earlier to give nulls special treatment) IF ISNULL(@i, 0) = 0 -- treat nulls as zero IF ISNULL(@i, -1) = 0 -- treat nulls as NON-zero

RE: Unknown NULLs

kramaswamy — Tue, 25 Aug 2009 13:57:05 GMT

[quote][b]SQAPro (8/25/2009)[/b][hr]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![/quote]'Course that's dependent upon ANSI_NULLS :P

RE: Unknown NULLs

SQAPro — Tue, 25 Aug 2009 13:49:01 GMT

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!

RE: Unknown NULLs

notquitexena — Fri, 14 Aug 2009 15:24:29 GMT

[quote][b]kramaswamy (8/14/2009)[/b][hr]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.[/quote]Thanks for clarifying.

RE: Unknown NULLs

kramaswamy — Fri, 14 Aug 2009 12:46:39 GMT

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.

RE: Unknown NULLs

notquitexena — Fri, 14 Aug 2009 12:28:17 GMT

[quote][b]kramaswamy (8/13/2009)[/b][hr]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 #1If @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 #2if @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 #3If 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.[/quote]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.

RE: Unknown NULLs

Kevin Gill — Fri, 14 Aug 2009 02:25:21 GMT

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 routeNULL <> NULL returns UNKNOWN so 'IF NULL <> NULL will go down the ELSE routeNULL IS NULL returns TRUE so 'IF NULL IS NULL will go down the IF routeNULL 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. ThereforeWith ANSI NULLS OFF :NULL = NULL returns TRUE so 'IF NULL = NULL will go down the IF routeNULL <> NULL returns FALSE so 'IF NULL <> NULL will go down the ELSE routeNULL IS NULL returns TRUE so 'IF NULL IS NULL will go down the IF routeNULL 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

RE: Unknown NULLs

kramaswamy — Thu, 13 Aug 2009 11:57:08 GMT

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 #1If @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 #2if @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 #3If 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.

RE: Unknown NULLs

SanjayAttray — Thu, 13 Aug 2009 11:50:35 GMT

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 ONi is nulli <> 0i = 0SET ANSI_NULLS OFFi is nulli <> 0i <> 0

RE: Unknown NULLs

Edward01 — Thu, 13 Aug 2009 11:38:43 GMT

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).aspxand 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!

RE: Unknown NULLs

Steve Jones - SSC Editor — Thu, 13 Aug 2009 09:36:24 GMT

Yep, code tags changed. formatting corrected.

RE: Unknown NULLs

Chad Crawford — Thu, 13 Aug 2009 08:44:35 GMT

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.

RE: Unknown NULLs

kramaswamy — Thu, 13 Aug 2009 05:34:51 GMT

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

RE: Unknown NULLs

Kevin Gill — Thu, 13 Aug 2009 01:45:44 GMT

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...

RE: Unknown NULLs

BrainDonor — Thu, 13 Aug 2009 01:42:33 GMT

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.

Unknown NULLs

Condorman — Wed, 12 Aug 2009 21:50:49 GMT

Comments posted to this topic are about the item [B]Unknown NULLs[/B]