Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 set ansi_nulls Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, June 1, 2010 8:09 PM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, November 23, 2016 1:21 AM Points: 1,030, Visits: 955
Post #931057
 Posted Tuesday, June 1, 2010 8:12 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, June 30, 2015 9:47 AM Points: 2,160, Visits: 2,204
 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 Group: General Forum Members Last Login: Wednesday, July 29, 2015 11:50 PM Points: 1,967, Visits: 1,189
 good question.Excellent explanation. MalleswarareddyI.T.AnalystMCITP(70-451)
Post #931092
 Posted Tuesday, June 1, 2010 10:59 PM
 SSCommitted Group: General Forum Members Last Login: Wednesday, July 29, 2015 11:50 PM Points: 1,967, Visits: 1,189
 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 OFFSET XACT_ABORT ONGOCREATE 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. MalleswarareddyI.T.AnalystMCITP(70-451)
Post #931094
 Posted Tuesday, June 1, 2010 11:13 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, June 30, 2015 9:47 AM Points: 2,160, Visits: 2,204
 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 Group: General Forum Members Last Login: Wednesday, July 29, 2015 11:50 PM Points: 1,967, Visits: 1,189
 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 MalleswarareddyI.T.AnalystMCITP(70-451)
Post #931107
 Posted Wednesday, June 2, 2010 1:26 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, November 23, 2016 1:21 AM Points: 1,030, Visits: 955
 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 Group: General Forum Members Last Login: Thursday, December 31, 2015 5:26 AM Points: 1,212, Visits: 798
 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 Group: General Forum Members Last Login: Tuesday, April 19, 2016 5:42 AM Points: 2,831, Visits: 374
 sharath.chalamgari (6/2/2010)if you use IS NULL then there will not be any effect on the ANSI_NULLS settingsagreed... 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
 SSCommitted Group: General Forum Members Last Login: Wednesday, October 5, 2016 3:46 AM Points: 1,699, Visits: 1,323
 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

 Permissions