set ansi_nulls

  • david.wright-948385

    SSCarpal Tunnel

    Points: 4028

    Comments posted to this topic are about the item set ansi_nulls

  • UMG Developer

    SSChampion

    Points: 13482

    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

    good question.Excellent explanation.

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

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    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

    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

    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: 4028

    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

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

  • ziangij

    SSCertifiable

    Points: 7211

    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

    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

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    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

    SSCrazy

    Points: 2971

    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

    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: 281243

    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

    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.

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

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