Hugo Kornelis (8/16/2012)
Lokesh Vij (8/14/2012)
I think any comparison against NULL will yield Unknown, which is not True, and therefore records won't be returned.
Yes that's correct!
And even worse the answer is incorrect with ANSI NULLS off.
Nope. The ANSI_NULLS option only affects the result of a comparison between two NULL values. With ANSI_NULLS on (the default, and in the future the only option), both NULL = NULL and NULL <> NULL evaluate to Unknown; with ANSI_NULLS off, they evaluate to True and False respectively.
This setting has no impact at all on this question.
Sorry, I don't agree:
set ansi_nulls off
select 1 where 1 <> null
Hmmm, interesting. Good, solid evidence. However, when I add SET ANSI_NULLS OFF or SET ANSI_NULLS ON to the code in the QotD, I does not affect the number of rows returned. So how do you explain that?
My previous post was indeed incorrect. I wrote it after I found that changing the ANSI_NULLS setting didn't affect the result (on SQL 2012). My first theory was that the deprecation path had made this an option that will be ignored, but I could not find anything in Books Online to support that theory. So I had to find another explanation. After reading Books Online and misinterpreting some text, I wrote the incorrect explanation in my first post.
I should have read a bit further. On the page that describes SET ANSI_NULLS (see http://msdn.microsoft.com/en-us/library/ms188048.aspx), it says explicitly:
"SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison."
You may argue that this text does not literally apply here (there is no NULL variable or constant, but neither are both sides columns or compoud expressions). But the first part of the sentence is very clear: one of the operands has to be either the keyword NULL, or a variable with the NULL value. Not a column with the NULL value.
So while my original explanation was completely wrong, my observation that SET ANSI_NULLS has no effect at all on this question was correct.