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.

malleswarareddy_m (6/1/2010)

---

This also gives the same answer please check it.and read explanation carefully.

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;

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;

UMG Developer (6/1/2010)

---

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.

sharath.chalamgari (6/2/2010)

---

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