What does `SET ANSI_NULLS OFF` do?

Question

What does SET ANSI_NULLS OFF do?

Answer 1

SET ANSI_NULLS OFF instructs the server to evaluate statements involving NULL using non-standard semantics.

SET ANSI_NULLS OFF;
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END; -- Evaluates to 1 (bad!)
SET ANSI_NULLS ON;
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END; -- Evaluates to 0 (good!)

You should never create new code with with the non-standard semantics setting of SET ANSI_NULLS OFF because:

• It is never necessary,
• For database queries to behave with the rich semantics that occur when NULL is treated differently from any other value (for instance in a WHERE clause), values compared with NULL should always return False / UNKNOWN,
• It makes code that is more difficult to maintain since developers might not realize it is using the nonstandard setting, or be confused by it, and
• Microsoft has warned that in a future version of SQL Server that the setting will cause an explicit error.