What is ANSI_NULLS and why will I be glad when it finally goes away?
13July 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
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).
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
Thanks! Glad you liked it. We will see if ANSI_NULLS ends up being one of those several release “depreciated” features or not but I’ll certainly be glad when it’s gone.
“deprecated”
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.
Oh yes, vendor code is always a pleasure.
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.
Glad you like it. It’s one of those things that occasionally trip up even really experienced DBAs. A number of DBAs do their best to avoid allowing NULLs at all.
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.
Interesting. I didn’t know that. That would explain a lot. Thanks for the info!
Superb explanation ,Thanks……………………………..
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!
I really appreciate that. Thanks for the compliment.
[…] ANSI_NULLS does not appear to have any effect on COUNT. […]