set ansi_nulls

Question

set ansi_nulls

david.wright-948385

SSCarpal Tunnel

Points: 4041
More actions
June 1, 2010 at 8:09 pm

#222150

Comments posted to this topic are about the item set ansi_nulls

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

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.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 2

good question.Excellent explanation.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

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)

UMG Developer SSChampion Points: 13482 More actions · Answer 4

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.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 5

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)

david.wright-948385 SSCarpal Tunnel Points: 4041 More actions · Answer 6

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:

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 7

if you use IS NULL then there will not be any effect on the ANSI_NULLS settings

ziangij SSCrazy Eights Points: 8066 More actions · Answer 8

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.

honza.mf SSCertifiable Points: 5519 More actions · Answer 9

Most difficult thing with this question was thinking where the trick is hidden.

I understand author has a block to write null=null

🙂

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 10

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

Rose Bud Hall of Fame Points: 3147 More actions · Answer 11

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.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 12

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

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

cengland0 SSCertifiable Points: 6102 More actions · Answer 14

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.