ANSI_NULLS

  • It is quite good question i think everyone knows but still don't know, I hope i am making sense like the question 🙂

  • Paul White NZ (4/7/2010)


    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.

    The only time I can imagine it being useful is when you've inherited a DB with a lot of SQL scattered around embedded in application code that relies on the setting because that's the way it was designed by the IT development team on the Ark.

    If you are lucky, that will never happen to you. I reckon most people will be lucky.

    Edit: just realised there's another use: to run code or get execution plans for QsOTD that specify it:hehe:.

    Tom

  • Nice clear question, correct answer, nice clear explanation.

    The success rate is amazing. When I read the question I thought there would be a large majority getting it right - obviously I was wrong about that.

    Tom

  • Tom.Thomson (4/7/2010)


    Nice clear question, correct answer, nice clear explanation.

    The success rate is amazing. When I read the question I thought there would be a large majority getting it right - obviously I was wrong about that.

    Thank you, Tom.

    As far as the prevalence of ANSI_NULLS OFF in the wild...my personal experience is that it is pretty common - but not in well-organized shops with standards 😉

  • 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.

    Be aware! This setting is per connection, not per server. And SP keep the setting of the connection they are created in.

    Remarked by a person that lives in environment, when ANSI NULLS are (due to history) moving from OFF to ON :crazy:



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Toreador (4/7/2010)


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

    Only when you need negative comparisons.

    A <> 1 doesn't return rows where A is null with ANSI NULLS ON. Such queries must be rewritten.

    With ANSI NULLS OFF, such conditions are much more obvious.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • honza.mf (4/7/2010)


    Be aware! This setting is per connection, not per server. And SP keep the setting of the connection they are created in. Remarked by a person that lives in environment, when ANSI NULLS are (due to history) moving from OFF to ON :crazy:

    I agree about the craziness - some quite subtle bugs can, and do, arise.

    You are also right about the creation settings being carried around with the objects. I posted a short (and obvious) script earlier in the discussion to identify objects with ANSI_NULLS OFF.

    Thanks for taking the time to comment on my question!

  • Paul White NZ (4/7/2010)


    honza.mf (4/7/2010)


    Be aware! This setting is per connection, not per server. And SP keep the setting of the connection they are created in. Remarked by a person that lives in environment, when ANSI NULLS are (due to history) moving from OFF to ON :crazy:

    I agree about the craziness - some quite subtle bugs can, and do, arise.

    You are also right about the creation settings being carried around with the objects. I posted a short (and obvious) script earlier in the discussion to identify objects with ANSI_NULLS OFF.

    Thanks for taking the time to comment on my question!

    Nice question, nice explanation.

    I hate this setting, so I know quite a lot about it :blink:

    There is only one complication with negative queries I mentioned above. Easy to solve but hard to explain to (some) non-developpers. "I want to see all records that don't have..."



    See, understand, learn, try, use efficient
    © Dr.Plch

  • honza.mf (4/7/2010)[hrThere is only one complication with negative queries I mentioned above. Easy to solve but hard to explain to (some) non-developpers. "I want to see all records that don't have..."

    Quite, but OFF is deprecated, so we don't want to be using that in new work, right? 😉

    Don't get me started on how NULLs complicate queries and designs unnecessarily...:hehe:

  • Very interesting question. I found that adding this case:

    SELECT 'E' FROM @T WHERE A IS NULL;

    will return a row with ANSI_NULLS ON or OFF. This may be the approach to use for consistent results.

    Dave

  • Great question Paul.... when included with the script for the procs, it's good enough to be turn into an article...

  • Ninja's_RGR'us (4/7/2010)


    Great question Paul.... when included with the script for the procs, it's good enough to be turn into an article...

    Thank you very much! :blush:

    I will consider that.

    @dave-3: Absolutely right! IS [NOT] NULL is the righteous way to test for NULL. 🙂

  • Nice question. I always am amazed the things I learn here. 🙂

  • Paul White NZ (4/7/2010)


    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.

    This is a very good question and excellent explanation. Thank you Paul.

    The best part for me was to thoroughly enjoy one more read about the ansi_nulls off going away. I cannot wait for this to happen. It would be interesting to see how the migration of the old procs is going to go. Since ansi_nulls is one of the 2 "sticky" settings, there might be a plenty of procs out there which accidentally inherited the settings from the connections which was used to create them. What I mean is that if someone creates a proc on the connection which has the setting off due to user options set incorrectly or for whatever reason then doing the right thing inside the body of the proc does not unfortunately have any effect. For example:

    -- produce the evil connection-level setting

    set ansi_nulls off;

    go

    create proc dbo.dump_me as

    begin

    declare @t table (A int null);

    insert into @t(A) values (null);

    -- the cave man attempt to do the right thing. This

    -- usually works, but not in the stored proc as the

    -- latter inherited the "sticky" set ansi_nulls off

    -- from the conncection :(

    set ansi_nulls on;

    select 'A' result from @t where A = null;

    end;

    go

    -- execute the proc hoping to get no records. The attempt is

    -- futile because the set ansi_nulls on inside of the proc

    -- body will never be honored

    exec dbo.dump_me;

    go

    This results in

    result

    ------

    A

    Time will tell I guess.

    Oleg

  • Oleg Netchaev (4/7/2010)


    This results in

    result

    ------

    A

    I'm lost for words!

Viewing 15 posts - 16 through 30 (of 81 total)

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