Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

What is ANSI_NULLS and why will I be glad when it finally goes away?

Recently I wrote about what it means that a value is NULL. Right at the beginning I mentioned ANSI_NULLS and said I would discuss it later. Well it’s later.

In the previous post I discussed how NULL is basically “unknown”. And any time you compare something to “unknown” you can’t possibly be certain of the answer so you get back an answer of “unknown”. ANSI_NULLS is simply a setting that changes this behavior. When ANSI_NULLS is set to ON then NULLs follow the ISO standard which is what I have been describing. When ANSI_NULLS is set to OFF then a comparison of NULL=NULL or NULL<>NULL returns an actual answer (TRUE & FALSE respectively) instead of an unknown answer (NULL). Basically it makes NULL into a value of its own.

Personally I feel like this setting has caused a great deal of the confusion surrounding NULLs. Which is one of the reasons I’m pleased that it’s going away. For the purists out there yes I realize it isn’t technically going away. It will be there but always set to ON. Same effect as far as I’m concerned.

So why the blame? Well first of all having two different behaviors for something as already complicated as NULLs is probably not the best idea. Also there is its effect on code …

Here are two stored procedures:

SET ANSI_NULLS ON
GO
CREATE PROCEDURE ANSI_NULL_1
AS
IF NULL = NULL 
	PRINT OBJECT_NAME(@@PROCID) + ': Equal'
ELSE
	PRINT OBJECT_NAME(@@PROCID) + ': Not Equal'
GO

SET ANSI_NULLS OFF
GO
CREATE PROCEDURE ANSI_NULL_2
AS
IF NULL = NULL 
	PRINT OBJECT_NAME(@@PROCID) + ': Equal'
ELSE
	PRINT OBJECT_NAME(@@PROCID) + ': Not Equal'
GO

Note that the code is exactly the same for both stored procedures.

EXEC ANSI_NULL_1
EXEC ANSI_NULL_2

ANSI_NULLS_1

Now if you read the creation code you probably noticed that I set ANSI_NULLS differently for each SP which is why the different results. No big deal here, it’s pretty obvious. But let’s roll forward a couple of years (go ahead, I’ll wait). So now we have had some turn over and a whole new group of DBAs & developers are looking at these SPs.

ANSI_NULLS_2

No real difference in Object Explorer. We look at the code. Still no difference. So why are we getting different results!?! About now everyone starts to pull their hair out (not me, I don’t have that much left). Hopefully someone eventually says “I wonder if it’s the ANSI_NULLS setting?” and “How do we check?” And they hit the internets for a bit and with any luck find something along the lines of this:

SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules

ANSI_NULLS_3

So now we know which ones have ANSI_NULLS turned on and which turned off. Say we have 200 or so SPs of which half of them have ANSI_NULLS turned off. We want to be consistent and for that matter let’s get ready for the day when ANSI_NULLS is always going to be ON. Again searching the internets you come up with two options.

  • You can use a script in the scripting language of your choice (but not T-SQL).
  • You can manually script out each SP and re-create it using ANSI_NULLS ON.

 
ANSI_NULLS is ONLY set at creation time. You can’t change it later without re-creating or altering the code with the new setting.

Eventually you have made all the changes, but now your users are screaming because they are getting odd results all over the place. So now you either have to change all the SPs back (hope you kept a list) or fix all of the code that assumed ANSI_NULLS were OFF.

….
Some time later
….

Whew, everything is fixed and all of the SPs are ANSI_NULLs ON. Now let’s get to work on functions!

You can see why I consider this a <heavily sarcastic tone> bit </heavily sarcastic tone> of a pain. In particular because frequently SPs are created without anyone looking at this setting and get created with ANSI_NULLS OFF by accident.

Ok, conclusion. NULLs are confusing. To help with this ANSI_NULLS should always be set ON. If you find you have a bunch of code where ANSI_NULLS was OFF at creation time you should probably consider changing it, but ONLY and I repeat ONLY after heavily testing the ramifications (remember NULLs are confusing at the best of times).


Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, sql statements, T-SQL

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...