Paul White NZ (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;
create proc dbo.dump_me as
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;
-- 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
This results in
Time will tell I guess.