ANSI_NULLS

  • Comments posted to this topic are about the item ANSI_NULLS

  • Nice question Paul.

    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

  • CirquedeSQLeil (4/6/2010)


    Nice question Paul.

    1. Did you get it right?

    2. Are you scared by the behaviour ANSI_NULLS OFF?

  • Paul White NZ (4/6/2010)


    CirquedeSQLeil (4/6/2010)


    Nice question Paul.

    1. Did you get it right?

    2. Are you scared by the behaviour ANSI_NULLS OFF?

    Yes I got it correct.

    I thought you explained things nicely.

    And yes, by having Ansi_Nulls Off, I can see there being complications. I will have to verify this setting on my servers.

    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

  • CirquedeSQLeil (4/6/2010)


    And yes, by having Ansi_Nulls Off, I can see there being complications. I will have to verify this setting on my servers.

    SELECT object_type = OBJ.type_desc,

    [schema_name] = SCHEMA_NAME(OBJ.[schema_id]),

    [object_name] = OBJ.name,

    SM.[definition]

    FROM sys.objects OBJ

    JOIN sys.sql_modules SM

    ON SM.[object_id] = OBJ.[object_id]

    WHERE OBJ.type_desc IN

    (

    N'SQL_STORED_PROCEDURE',

    N'SQL_SCALAR_FUNCTION',

    N'SQL_INLINE_TABLE_VALUED_FUNCTION',

    N'SQL_TABLE_VALUED_FUNCTION',

    N'SQL_TRIGGER',

    N'VIEW'

    )

    AND SM.uses_ansi_nulls = 0

    AND OBJ.is_ms_shipped = 0

    ORDER BY

    object_type,

    [schema_name],

    [object_name];

  • I agree... Good question. Made me sit back and think about this a bit.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Thanks for the question, Paul. Luckily, I have set ANSI_NULLS ON on my servers..:-)

  • ziangij (4/6/2010)


    Thanks for the question, Paul. Luckily, I have set ANSI_NULLS ON on my servers..:-)

    Good stuff.

    If you would like to check that no-one else has manually created a procedure, function, trigger, or view and overridden your default settings at the session level, run the script I posted before - just to check 🙂

  • Nice question.

    Maybe I read it too fast: "Select all...", I selected "ABCD", then pressed the "Submit" button. Oops, there was "...that apply" part 🙂

    It's interesting to explore the execution plans.

    The condition "= NULL" is replaced with the condition "IS NULL". Execution plans "A" and "C" both have the node "Table Scan(OBJECT: (@T), WHERE: ([A] IS NULL))".

    Execution plan "B" has the only node "Constant Scan", which means SQL Server understands (at compile time!) that there will be no output rows.

    Execution plan "D" has the node "Table Scan(OBJECT: (@T), WHERE: ([A]=[A]))". This clarifies why "if both sides of the comparison are columns ... the setting [SET ANSI_NULLS] does not affect the comparison."

  • Is there any situation where setting ansi_nulls off is a good idea?

  • vk-kirov (4/7/2010)


    Nice question.

    Maybe I read it too fast: "Select all...", I selected "ABCD", then pressed the "Submit" button. Oops, there was "...that apply" part 🙂

    Ha - yes it was terribly misleading, I'll ask Steve to credit the points to you :w00t:

    It's interesting to explore the execution plans.

    The condition "= NULL" is replaced with the condition "IS NULL". Execution plans "A" and "C" both have the node "Table Scan(OBJECT: (@T), WHERE: ([A] IS NULL))".

    Execution plan "B" has the only node "Constant Scan", which means SQL Server understands (at compile time!) that there will be no output rows.

    Execution plan "D" has the node "Table Scan(OBJECT: (@T), WHERE: ([A]=[A]))". This clarifies why "if both sides of the comparison are columns ... the setting [SET ANSI_NULLS] does not affect the comparison."

    I am so glad you took the time to do that - I had great fun putting this question together, which included examining the query plans and having much the same reactions as you. I was particularly pleased with the = ANY version. Of course, some people will just run the code, I expect :Wow:

  • Toreador (4/7/2010)


    Is there any situation where setting ansi_nulls off is a good idea?

    I have never come across one. Apart from the bizarre rules illustrated by the question, ANSI_NULLS OFF is going away in a future version. Attempting to set ANSI_NULLS OFF will generate an error message in that future version. Will be a huge change for some people.

  • Good QOD, got my brain cells working and had reindex all the chapters in my brain but still got it worng , thank you , A

  • Paul White NZ (4/7/2010)


    I have never come across one.

    Good - I won't worry too much that I got the answer wrong 😉

  • Less than 50% pass rate so far :w00t:

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

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