A Two Million Dollar Misunderstanding
If you think that this article will be confined to academic subtleties of no interest to real-world DBAs, let me first mention that misunderstanding the meaning of NULL cost a client of mine at least two million dollars.
We will discuss the specifics of how it happened but first we should review the concept of Null and how it could have figured in this fiasco.
The Value of NULL
Null is not zero. Null is not an empty string. Null is not a value at all. Most importantly for our discussion, one null value does not equal any other null value. In the relational database model, Null simply means that a value is not known.
However, it is not so simple for a lay-person to grasp the full implications of that statement in regard to relational databases. Those untrained in database concepts tend to think of Null as a value and that a logical comparison of two Null values should be true.
In early versions, SQL Server was a departmental scale database and it was very often implemented and programmed by intelligent but completely untrained persons. It is for that reason, I suppose, that Microsoft put the Set ANSI_NULLS OFF option in SQL Server. This option reverses the ANSI standard for relational databases and makes NULL = NULL expressions evaluate to True.
This was a bad idea that was compounded by the fact that this setting could be changed at the statement level, the connection level and the database level. Because of the number of places this option could be set, is not immediately obvious when viewing TSQL code how a NULL=NULL expression is going to be evaluated.
An Expensive Misunderstanding
My client marketed its products using television infomercials. The database was relatively quiet much of the time but during the infomercials the phones lit up and a huge roomful of operators started entering leads into a staging table. After the rush was over, the leads were processed and sent to the salespersons, who then called the leads and attempted to close a sale.
I was brought in because the sales manager noticed small but consistent differences between the number of phone calls taken and the number of leads actually entered in the system. I looked at their methodology and found that the leads could be processed by operators using two different client machines. One machine was used much more often than the other but each client machine executed identical TSQL code on the SQL Server. I dug into the code and found a comparison of one value to another using an equality condition (=). Data in this comparison could potentially be Null. If the comparison returned True, the lead was not marked for follow-up and was never acted on. Knowing that, you may have already guessed what the problem turned out to be.
On the more commonly used machine, the connection to the sql server used the default setting ANSI_NULLS ON. Any equality comparison of NULL values would return False and the lead record would be marked for processing.
On the less commonly used machine, the connection to SQL Server used the ANSI_NULLS OFF setting. On this machine, when both compared values were Null, the comparison evaluated to True and dropped the lead record into a black hole. This is exactly the opposite of what was intended and what happened on the correctly configured first server.
This only affected a small percentage of leads processed on the second machine, otherwise the anomaly would have been noticed far earlier. However, over time, the collection of lost leads had grown to hundreds of thousands of lost opportunities.
We never found out how the ANSI_NULL setting came to be different on the two machines but we did find out how expensive the mistake had been.
The client asked me to retrieve a count of the leads that had not been processed due to this problem. They knew to an accuracy of several decimal points what their conversion rate would have been if these inquiries had been processed correctly.
Based on the number I gave them, they told me that they had lost at least two million dollars in revenue because of this problem.
Avoiding The Problem
It is simple to avoid this problem. For comparisons of null and not null values, do not use the = or <> operators. Instead use these forms of syntax:
WHERE columnname IS NOT NULL or WHERE columnname IS NULL
For other types of Null comparisons, you can also use the ISNULL() function.
These forms will return consistent results no matter how ANSI_NULLS is set.
The Final Fix
With the release of SQL Server 2008 R2, Microsoft deprecated the ANSI_NULLS OFF setting, meaning they will remove it in some future version. When that finally happens, this kind of mistake will not be possible, but it is still possible to make this same mistake in SQL Server 2008 R2 and all earlier versions.
When this setting is finally removed, any code attempting to set ANSI_NULLS OFF will fail and return an error. You should avoid using it in new development and change all legacy code that uses it.