Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

set ansi_nulls Expand / Collapse
Author
Message
Posted Tuesday, June 1, 2010 8:09 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 986, Visits: 788
Comments posted to this topic are about the item set ansi_nulls
Post #931057
Posted Tuesday, June 1, 2010 8:12 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.
Post #931059
Posted Tuesday, June 1, 2010 10:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
good question.Excellent explanation.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #931092
Posted Tuesday, June 1, 2010 10:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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)
Post #931094
Posted Tuesday, June 1, 2010 11:13 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.
Post #931101
Posted Tuesday, June 1, 2010 11:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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)
Post #931107
Posted Wednesday, June 2, 2010 1:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 986, Visits: 788
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
Post #931142
Posted Wednesday, June 2, 2010 2:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:29 AM
Points: 1,179, Visits: 786
if you use IS NULL then there will not be any effect on the ANSI_NULLS settings
Post #931161
Posted Wednesday, June 2, 2010 3:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,003, Visits: 369
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.
Post #931188
Posted Wednesday, June 2, 2010 6:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:43 AM
Points: 1,353, Visits: 1,313
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
Post #931279
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse