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.