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