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

12

July 28, 2014 by Kenneth Fisher

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

12 thoughts on “What is ANSI_NULLS and why will I be glad when it finally goes away?

  1. Rick says:

    This is a very good point and important for us all to understand and be aware of. I retired after 42 years in IT but still ‘play’ with db’s and SQL code regularly. It is always good to be reminded of the details that make us better.

    Thanks

  2. J Ormsby says:

    Thanks for a great article. NULL handling seems to be one of those skills that aren’t covered well in books or a lot of training/education material. Have to learn by being burned. I work with a large 5TB 33-thousand table (healthcare gotta love it) database. Immediately ran the code to find USES_ANSI_NULLS = 0. Only a couple of locally developed items in the list (good), the rest came from third party vendors: SAP/BOE’s Crystal Reports ‘CR_*’ (appears frequently and may be consistent) and Epic Electronic Healthcare (only a few). These vendors are selling products that integrate with a wide variety of databases, so maybe it’s not surprising that they diverge from what others consider standard practice. We don’t want to/have the option to modify the outside vendor’s code. In some cases they have offered their procedures for use in locally developed stored procedures and functions, so proceed with caution. … Just one more NULL handling issue to consider.

  3. Hilda says:

    Thank you for this article, indeed the NULL comparison is complicated on it’s own. I don’t enjoy queries that other or even myself write when it contains searchable field including NULLs, it adds so much complication to the equation. I did a search on our test/dev/production servers and I was relieve to find only a handful of procedures using USES_ANSI_NULLS = 0, that was good.

    Thanks again.

  4. apeterson says:

    I believe that in the early releases of SQL Server (v4.21,6.0,6.5) the default action was effectively ANSI_NuLL = OFF. So I would guess that this is just one of those legacy things.

  5. Pappu Dewngan says:

    Superb explanation ,Thanks……………………………..

  6. Emil says:

    Thanks for this clear and easy-to-understand writeup. This will be my new go-to when needing to explain ANSI_NULLS to people. Oh, and I agree completely that we’ll all be better off when the option to set ANSI_NULLS off goes away!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: