June 1, 2010 at 8:09 pm
Comments posted to this topic are about the item set ansi_nulls
June 1, 2010 at 8:12 pm
Wow, I happened to be the first to answer it, and I got it right. 🙂
Though not for the reason you stated, as it makes no difference if ANSI_NULLS is set ON or OFF that CASE statement will always return EQUAL, as NULL is always IS NULL. (Or at least as far as I can tell.)
If the CASE statement had been NULL = NULL the story would have been different.
June 1, 2010 at 10:52 pm
good question.Excellent explanation.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
June 1, 2010 at 10:59 pm
UMG Developer (6/1/2010)
Wow, I happened to be the first to answer it, and I got it right. 🙂Though not for the reason you stated, as it makes no difference if ANSI_NULLS is set ON or OFF that CASE statement will always return EQUAL, as NULL is always IS NULL. (Or at least as far as I can tell.)
If the CASE statement had been NULL = NULL the story would have been different.
SET ANSI_NULLS OFF
SET XACT_ABORT ON
GO
CREATE PROCEDURE DBO.x
AS
SET ANSI_NULLS OFF
SELECT CASE WHEN NULL = NULL THEN 'EQUAL' ELSE 'NOT EQUAL'
END AS ANSWER
SET ANSI_NULLS ON
SELECT CASE WHEN NULL = NULL THEN 'EQUAL' ELSE 'NOT EQUAL'
END AS ANSWER
GO
SET ANSI_NULLS OFF
EXEC DBO.X
SET ANSI_NULLS ON
EXEC DBO.X
DROP PROCEDURE DBO.X
This also gives the same answer please check it.and read explanation carefully.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
June 1, 2010 at 11:13 pm
malleswarareddy_m (6/1/2010)
UMG Developer (6/1/2010)
Wow, I happened to be the first to answer it, and I got it right. 🙂Though not for the reason you stated, as it makes no difference if ANSI_NULLS is set ON or OFF that CASE statement will always return EQUAL, as NULL is always IS NULL. (Or at least as far as I can tell.)
If the CASE statement had been NULL = NULL the story would have been different.
This also gives the same answer please check it.and read explanation carefully.
But like I said the explanation has nothing to do with the results in this example. Change all of them to SET ANSI_NULLS ON and try it, then set all of them to SET ANSI_NULLS OFF and try it and you will get the same results of EQUAL for everything. What he says may be true about how the settings sticks with the SP, but it doesn't change the results one bit.
For example try running this:
SET ANSI_NULLS ON;
SELECT CASE WHEN NULL IS NULL THEN 'EQUAL' ELSE 'NOT EQUAL' END AS ANSWER;
SET ANSI_NULLS OFF;
SELECT CASE WHEN NULL IS NULL THEN 'EQUAL' ELSE 'NOT EQUAL' END AS ANSWER;[/CODE]
You will get EQUAL for both, but with this:
SET ANSI_NULLS ON;
SELECT CASE WHEN NULL = NULL THEN 'EQUAL' ELSE 'NOT EQUAL' END AS ANSWER;
SET ANSI_NULLS OFF;
SELECT CASE WHEN NULL = NULL THEN 'EQUAL' ELSE 'NOT EQUAL' END AS ANSWER;[/CODE]
you will not. (At least on my SQL Server 2008 R2 instance.)
What I am trying to say is that the ANSI_NULLS setting will not change how "NULL IS NULL" is evaluated, but it will change how "NULL = NULL" is evaluated.
June 1, 2010 at 11:30 pm
UMG Developer (6/1/2010)
malleswarareddy_m (6/1/2010)
UMG Developer (6/1/2010)
Wow, I happened to be the first to answer it, and I got it right. 🙂Though not for the reason you stated, as it makes no difference if ANSI_NULLS is set ON or OFF that CASE statement will always return EQUAL, as NULL is always IS NULL. (Or at least as far as I can tell.)
If the CASE statement had been NULL = NULL the story would have been different.
This also gives the same answer please check it.and read explanation carefully.
But like I said the explanation has nothing to do with the results in this example. Change all of them to SET ANSI_NULLS ON and try it, then set all of them to SET ANSI_NULLS OFF and try it and you will get the same results of EQUAL for everything. What he says may be true about how the settings sticks with the SP, but it doesn't change the results one bit.
For example try running this:
SET ANSI_NULLS ON;SELECT CASE WHEN NULL IS NULL THEN 'EQUAL' ELSE 'NOT EQUAL' END AS ANSWER;
SET ANSI_NULLS OFF;
SELECT CASE WHEN NULL IS NULL THEN 'EQUAL' ELSE 'NOT EQUAL' END AS ANSWER;[/CODE]
You will get EQUAL for both, but with this:
SET ANSI_NULLS ON;SELECT CASE WHEN NULL = NULL THEN 'EQUAL' ELSE 'NOT EQUAL' END AS ANSWER;
SET ANSI_NULLS OFF;
SELECT CASE WHEN NULL = NULL THEN 'EQUAL' ELSE 'NOT EQUAL' END AS ANSWER;[/CODE]
you will not. (At least on my SQL Server 2008 R2 instance.)
What I am trying to say is that the ANSI_NULLS setting will not change how "NULL IS NULL" is evaluated, but it will change how "NULL = NULL" is evaluated.
you are correct
Malleswarareddy
I.T.Analyst
MCITP(70-451)
June 2, 2010 at 1:26 am
Absolutely right - my bad. I'm afraid my brain is wired to never ever use '=' or '<>' where either side can be null, even if I'm trying to make a point about exactly that :doze:
June 2, 2010 at 2:10 am
if you use IS NULL then there will not be any effect on the ANSI_NULLS settings
June 2, 2010 at 3:26 am
sharath.chalamgari (6/2/2010)
if you use IS NULL then there will not be any effect on the ANSI_NULLS settings
agreed... i executed the query on sql 2005; ANSI NULLS on/off doesn't make any difference.
June 2, 2010 at 6:16 am
Most difficult thing with this question was thinking where the trick is hidden.
I understand author has a block to write null=null
🙂
June 2, 2010 at 7:39 am
david.wright-948385 (6/2/2010)
Absolutely right - my bad. I'm afraid my brain is wired to never ever use '=' or '<>' where either side can be null, even if I'm trying to make a point about exactly that :doze:
The really funny thing is, as I'm re-reading the question several times trying to find the "gotcha," I thought to myself, "You know, I'll bet the author meant for one of these to say '= NULL' and goofed up." 😀
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
June 2, 2010 at 8:56 am
Your finger muscle memory wouldn't allow you to write null=null! FWIW, I think the error helps emphasize the point about being careful when dealing with nulls.
June 2, 2010 at 11:05 am
I read this carefully and thought to myself, "gee, why didn't the author use = Null, that would have been a better question." I answered the question, and read the forum responses....and lo and behold it was supposed to be = Null. Usually this is the sort of thing I will get wrong because I frequently read intent into the question and answer according to intent.
June 2, 2010 at 1:59 pm
Thanks for the nice question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 3, 2010 at 5:06 am
I too got it right but for the wrong reason. I knew that using Null is Null will always be true (regardless of the setting of ANSI_NULLS) so that's why I got the right answer not because of what the ANSI_NULLS setting was during the creation of the stored procedure. I learned something today and glad I guessed correctly.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply