SQL: SET ANSI_NULLS ON/OFF

SET ANSI_NULLS ON/OFF 

QUOTED_IDENTIFIER

QUOTED_IDENTIFIER

This setting controls how quotation marks ".." are interpreted by the SQL compiler. When QUOTED_IDENTIFIER is ON then quotes are treated like brackets ([...]) and can be used to quote SQL object names like table names, column names, etc. When it is OFF (not recommended), then quotes are treated like apostrophes ('..') and can be used to quote text strings in SQL commands.

ANSI_NULLS

This setting controls what happens when you try to use any comparison operator other than IS on NULL. When it is ON, these comparisons follow the standard which says that comparing to NULL always fails (because it isn't a value, it's a Flag) and returns FALSE. When this setting is OFF (really not recommended) you can sucessfully treat it like a value and use =, <>, etc. on it and get back TRUE as appropiate.

The proper way to handle this is to instead use the IS (ColumnValue IS NULL ..).

In SQL Server, what does “SET ANSI_NULLS ON” mean?

SET ANSI_NULLS ON/OFF: The ANSI_NULLS option specifies that how SQL Server handles the comparison operations with NULL values. When it is set to ON any comparison with NULL using = and <> will yield to false value. And it is the ISO defined standard behavior.


~

Returns one row. So even when both operands are columns, NULL does not equal NULL. And thedocumentation for = doesn't have anything to say about the operands:

When you compare two NULL expressions, the result depends on the ANSI_NULLS setting:

If ANSI_NULLS is set to ON, the result is NULL¹, following the ANSI convention that a NULL(or unknown) value is not equal to another NULL or unknown value.

If ANSI_NULLS is set to OFF, the result of NULL compared to NULL is TRUE.

Comparing NULL to a non-NULL value always results in FALSE².

However, both ¹ and ² are incorrect - the result of both comparisons is UNKNOWN.

---

^*The cryptic meaning of this text was finally discovered years later. What it actually means is that, for those comparisons, the setting has no effect and it always acts as if the setting were ON. Would have been clearer if it had stated that SET ANSI_NULLS OFF was the setting that had no affect.