April 7, 2010 at 2:50 am
It is quite good question i think everyone knows but still don't know, I hope i am making sense like the question 🙂
April 7, 2010 at 3:05 am
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
April 7, 2010 at 3:09 am
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
April 7, 2010 at 3:52 am
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
April 7, 2010 at 4:23 am
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:
April 7, 2010 at 4:26 am
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.
April 7, 2010 at 4:26 am
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
April 7, 2010 at 4:33 am
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..."
April 7, 2010 at 4:36 am
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
April 7, 2010 at 6:44 am
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
April 7, 2010 at 6:45 am
Great question Paul.... when included with the script for the procs, it's good enough to be turn into an article...
April 7, 2010 at 6:54 am
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
April 7, 2010 at 8:37 am
Nice question. I always am amazed the things I learn here. 🙂
April 7, 2010 at 9:38 am
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
April 7, 2010 at 9:44 am
Oleg Netchaev (4/7/2010)
This results inresult
------
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