August 15, 2012 at 10:18 am
August 15, 2012 at 12:52 pm
mtassin (8/15/2012)
honza.mf (8/15/2012)
And even worse the answer is incorrect with ANSI NULLS off.
At leats this thankfully won't be an issue sooner or later. SET ANSI NULLS OFF will throw an error in a coming SQL Release.
Yes, but it's legal today. And sometimes one meets the yesterday.
August 15, 2012 at 12:55 pm
Thanks for an easy one!
August 15, 2012 at 2:58 pm
One of the very first things I learned, and somehow still occasionally forget, even now. 😛
August 15, 2012 at 11:25 pm
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 16, 2012 at 2:38 am
honza.mf (8/15/2012)
Lokesh Vij (8/14/2012)
rVadim (8/14/2012)
I think any comparison against NULL will yield Unknown, which is not True, and therefore records won't be returned.
+1
Yes that's correct!
+1
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.
EDIT: Please read the two following posts as well; the above explanation is not correct and a better explanation of why ANSI_NULLS does not affect the question is given in the next two messages.
August 16, 2012 at 4:21 am
Hugo Kornelis (8/16/2012)
honza.mf (8/15/2012)
Lokesh Vij (8/14/2012)
rVadim (8/14/2012)
I think any comparison against NULL will yield Unknown, which is not True, and therefore records won't be returned.
+1
Yes that's correct!
+1
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
August 16, 2012 at 4:46 am
honza.mf (8/16/2012)
Hugo Kornelis (8/16/2012)
honza.mf (8/15/2012)
Lokesh Vij (8/14/2012)
rVadim (8/14/2012)
I think any comparison against NULL will yield Unknown, which is not True, and therefore records won't be returned.
+1
Yes that's correct!
+1
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 offselect 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?
(later)
Found it!
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.
August 16, 2012 at 4:58 am
Hugo Kornelis (8/16/2012)
honza.mf (8/16/2012)
Sorry, I don't agree:
set ansi_nulls offselect 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?
(later)
Found it!
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.
Yes, this is OK, sorry for the first mistake.
Either way, ANSI NULLS off are an evil.
August 16, 2012 at 10:19 am
Stewart "Arturius" Campbell (8/15/2012)
Good back-to-basics question, thanks.However, as others have stated, the explanation is lacking a wee bit.
Is a wee bit an eighth of a wee byte?
August 21, 2012 at 12:52 am
It drives me nuts to see column definitions without a NULL or NOT NULL constraint.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 30, 2012 at 9:58 pm
Hi,
Sorry i tried, but failed to understand what u r saying...
I tried with the below query
CREATE TABLE #table1
(c1 INT, c2 INT)
INSERT INTO #table1 VALUES(1,2),(2,NULL),(3,2),(4,NULL),(5,3),(6,4)
select * from #table1 where c2<>2
As per the post, NULL may be NOT EQUAL to 2, so we should get rows with NULL values as well...
But, I'm not getting the rows with NULL values. Just getting rows with c2=3 and 4.
Please make me understand here. This may be very basic....:)
August 30, 2012 at 11:07 pm
Hi Use
Not exist .
as it uses contain search.
http://msdn.microsoft.com/en-us/library/ms188336.aspx
AND
<> (Not equal or =equal) uses match search .
NULL is undefined data that is why in match search it will never come.
SET ANSI_NULLS is deprecated in 2005 ..
August 31, 2012 at 1:28 am
g_one_2020 (8/30/2012)
Hi,Sorry i tried, but failed to understand what u r saying...
I tried with the below query
CREATE TABLE #table1
(c1 INT, c2 INT)
INSERT INTO #table1 VALUES(1,2),(2,NULL),(3,2),(4,NULL),(5,3),(6,4)
select * from #table1 where c2<>2
As per the post, NULL may be NOT EQUAL to 2, so we should get rows with NULL values as well...
But, I'm not getting the rows with NULL values. Just getting rows with c2=3 and 4.
Please make me understand here. This may be very basic....:)
I just typed a lengthy reply - and then got an error when trying to post it. And the "back" button on my browser gave me a different error screen instead of returning to the screen where I had typed my post, so I was unable to use copy and paste to try to post another time. I reallly hate it when that happens - please, site owners, FIX THIS!!!
I don't have the time to type that whole response again, so instead I will just refer you to my series of blog posts where I explain everything about NULLs:
1. NULL - The database's black hole
2. The logic of three-valued logic
3. Dr. Unknown, or how I learned to stop worrying and love the NULL
4. What if null if null is null null null is null?
September 1, 2012 at 6:30 pm
SQL Kiwi (8/21/2012)
It drives me nuts to see column definitions without a NULL or NOT NULL constraint.
What drives me nuts is that SQL doesn't default to NOT NULL when neither NULL nor NOT NULL is specified; defaulting to NULL, which for any schema competent designer is the exceptional case, is crazy (as is defaulting to exception generally).
Quite horrifyingly, I ticked the wrong point for this one. I think an insufficiency of either alcohol or of cafeine (or maybe of both) made me point the mouse at the wrong hole. I noticed while clicking submit, too late to not click.
Tom
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply