SQL Server – ANSI_NULLS, null and IS NOT NULL fun

I had a problem where i had a UDF on 2 databases with the same data that were returning different results.

The query that was running was basically:

SELECT COUNT(*) FROM Comment WHERE Date <> NULL

DB1 would give me a results of 3 and DB2 would give me a result of 0. If i ran that SQL in QA on either database i’d get 0.

It drove me nuts for ages, till I realised that the 2 databases had 1 slight difference..the UDF on DB1 had ANSI_NULLS OFF while DB2 had ANSI_NULLS ON.

So in summary, ANSI_NULLS OFF allows you to use the <> null form of a query as above, but ANSI_NULLS ON means the query should be written as follows:

SELECT COUNT(*) FROM Comment WHERE Date IS NOT NULL

As I don’t mess with the ANSI settings this one caugt me as I NEVER attempt to use the <> null version.