Instead of using the setting ANSI_NULLS = OFF, Isah will use the setting ANSI_NULLS = ON.

The setting ANSI_NULLS = OFF is declared deprecated by Microsoft. In addition, in the optimizer it is assumed that ANSI_NULLS has the value ON. The setting ANSI_NULLS = OFF can cause a performance penalty.

You can only use features such as indexed views and filtered indexes if ANS_NULLS = ON.

Certain processes and constructions behave as if ANSI_NULLS = ON, even if the actual value is OFF. Examples are replication and the comparison of two columns.

The ANSI_NULLS setting is part of the code of stored procedures, functions, views, triggers, and tables. If you change the setting for an object, you need to recompile this database object.

If ANSI_NULLS = OFF, the value NULL behaves like any other value. If you compare NULL values, one NULL equals another. Also, if you ask if values are different, the NULLS will turn out to be the same.

If ANSI_NULLS = ON, every expression with a NULL evaluates to UNKNOWN. In tests, UNKNOWN will behave the same as FALSE.

The Italic marked values show where the behavior changes.

There are exceptions:

• When you are comparing columns, the construction behaves as if ANSI_NULLS = ON, even when ANSI_NULLS = OFF.
• Triggers that fire in a replication context, behave as if ANSI_NULLS = ON, even when ANSI_NULLS = OFF.