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

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.

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

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