SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


set ansi_nulls


set ansi_nulls

Author
Message
david.wright-948385
david.wright-948385
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1252 Visits: 979
Comments posted to this topic are about the item set ansi_nulls
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2718 Visits: 2204
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
malleswarareddy_m
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2223 Visits: 1189
good question.Excellent explanation.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2223 Visits: 1189
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
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2718 Visits: 2204
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;



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;



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
malleswarareddy_m
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2223 Visits: 1189
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;



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;



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
david.wright-948385
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1252 Visits: 979
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
sharath.chalamgari
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 798
if you use IS NULL then there will not be any effect on the ANSI_NULLS settings
ziangij
ziangij
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3148 Visits: 376
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
honza.mf
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1955 Visits: 1323
Most difficult thing with this question was thinking where the trick is hidden.
I understand author has a block to write null=null
:-)



See, understand, learn, try, use efficient
© Dr.Plch
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search