Recently, I posted a link to show how to turn on ANSI_NULLS for a table. It's not normally easy to change that but we added a procedure to our free SDU Tools to make it easy.
But one of the comments I received was a question basically saying "OK, you've shown how to change it but you haven't mentioned why it matters in the first place".
Fair enough. So that's the topic of today's post.
Many developers (and database people) get confused about the handling of NULL values. When we talk about a value being NULL, what we're really saying is that it has no value. This is quite different to a number being zero, or a string being empty (or zero-length).
It's also why we can't compare a value to NULL by using an equals sign (or not equals), but by saying IS NULL or IS NOT NULL instead. Being NULL is a state that a variable or column is in, not a value that it holds.
It's worth noting that some database engines still don't even get this right. And SQL Server was one of those in its past.
Here's an example. In the WideWorldImporters database, there is a Sales.SpecialDeals table. It has many columns but when it was shipped, it had two rows. Let's look at those.
Note that if I try to select the rows where StockItemID = NULL, I get no rows but if I compare where StockItemID IS NULL, I get the rows.
This is because a WHERE clause returns rows where the value of the predicate is TRUE. But with ANSI_NULLS on, NULL does not equal NULL. A comparison of NULL and NULL returns NULL.
You can see how the comparison works with and without ANSI_NULLS ON here:
SET ANSI_NULLS OFF takes us back to the bad old days whereas SET ANSI_NULLS ON gives us ANSI standard SQL behavior on this. We don't want our tables declared with ANSI_NULLS OFF. (Or worse, I've seen some tables declared that way and others not).
You can read more here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql
Even though it would be bad practice to test for = NULL and you might still not care about that, ANSI_NULL must be on when dealing with computed columns and indexed views (as documented in the link given in the article), along with other settings like QUOTED_IDENTIFIERS and ARITHABORT.
It is a pity that different tools like SQLCMD, SSMS and JDBC based tooling have different default settings for these settings.
Agreed. I wish that out of the box, the defaults for all of them were the same. But based on historical settings, now it's unlikely to change because of backwards compatibility reasons.
However, setting ANSI_NULLS OFF is now deprecated. One day (hopefully), it will be gone.
ANSI NULLS ON is an unfortunate convention.
In languages where nullable types are a first class feature, null = null is the convention and rightly so. Functional languages like ML, Haskell, FSharp, and semi-functional languages like Scala and C#.
Using null to mean "unknown" is a possible use of an option type but very limiting.
Yep, but in ANSI SQL, NULL is "unknown". Values can be compared, but NULL isn't a value. It's the lack of a value. So it's the state that something is in, not the value that it has.