ANSI_NULLS

SET ANSI_NULLS { ON | OFF }

ANSI_NULLS is a definition that deal with the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

• When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
• When SET ANSI_NULLS is OFF, a SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

ANSI_NULLS definition was created in order to follow the ISO / SQL-92 rules.

http://msdn.microsoft.com/en-us/library/ms188048.aspx