Microsoft SQL Server ANSI NULLS


Example

From MSDN

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

ANSI NULLS being set to off allows for a =/<> comparison of null values.

Given the following data:

id someVal
 ----
 0 NULL
 1 1
 2 2

And with ANSI NULLS on, this query:

 SELECT id
 FROM table
 WHERE someVal = NULL

would produce no results. However the same query, with ANSI NULLS off:

 set ansi_nulls off

 SELECT id
 FROM table
 WHERE someVal = NULL

Would return id 0.