ANSI_NULLS

Question

ANSI_NULLS

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

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

Anil Gupta-301802 SSC Enthusiast Points: 166 More actions · Answer 1

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

TomThomson SSC Guru Points: 104773 More actions · Answer 2

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

TomThomson SSC Guru Points: 104773 More actions · Answer 3

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

Paul White SSC Guru Points: 150467 More actions · Answer 4

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 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

honza.mf SSCertifiable Points: 5519 More actions · Answer 5

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

honza.mf SSCertifiable Points: 5519 More actions · Answer 6

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

Paul White SSC Guru Points: 150467 More actions · Answer 7

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

honza.mf SSCertifiable Points: 5519 More actions · Answer 8

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

Paul White SSC Guru Points: 150467 More actions · Answer 9

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:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave62 SSCertifiable Points: 7459 More actions · Answer 10

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

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

Paul White SSC Guru Points: 150467 More actions · Answer 12

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 13

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

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 14

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

Toreador SSChampion Points: 11358 More actions · Answer 15

Oleg Netchaev (4/7/2010)
This results in
result
------
A

I'm lost for words!