Ansi null statement in SQL Server

The ANSI NULL setting in SQL Server controls how comparison with NULL values is handled. This is an important setting that affects query behavior and result.

SET ANSI NULL ON

Follow ISO standard behavior when any comparison with null return UNKOWN.  This is by default.

SET ANSI NULL OFF

It allows equality comparison with NULL value = null or <> null.

SET ANSI_NULLS ON  declare @a int =null; if (@a=null) select ' Null is equal to null' else select ' Null is not equal to null'  SET ANSI_NULLS OFF if (@a=null) select ' Null is equal to null' else select ' Null is not equal to null'

The following table shows how the setting of ANSI_NULLS affects the results of Boolean expressions using null and non-null values.

Boolean Expression	SET ANSI_NULLS ON	SET ANSI_NULLS OFF
NULL = NULL	UNKNOWN	TRUE
1 = NULL	UNKNOWN	FALSE
NULL <> NULL	UNKNOWN	FALSE
1 <> NULL	UNKNOWN	TRUE
NULL > NULL	UNKNOWN	UNKNOWN
1 > NULL	UNKNOWN	UNKNOWN
NULL IS NULL	TRUE	TRUE
1 IS NULL	FALSE	FALSE
NULL IS NOT NULL	FALSE	FALSE
1 IS NOT NULL	TRUE	TRUE

ANSI_NULLS on is required for indexed view, computed columns and future compatibility. It is very important to use is null or is not null, when we are checking for null value. It is highly recommended to always we SET ANSI_NULLS ON. The ANSI_NULLS setting significantly impacts NULL handing in our query, so it is important to understand and use it properly for consistent result.