Previous Topic

Next Topic

Inhoudsopgave

Book Index

ANSI_NULLS

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.

Construction \ Setting

ANSI_NULLS = OFF

ANSI_NULLS = ON

NULL = 1

FALSE

UNKNOWN

Which behaves like FALSE

NULL = NULL

TRUE

UNKNOWN

NULL <> 1

TRUE

UNKNOWN

NULL <> NULL

FALSE

UNKNOWN

Which behaves like FALSE

The Italic marked values show where the behavior changes.

There are exceptions: