ANSI_NULLS In SQL Server

ANSI_NULLS define the comparison rule for NULL values in SQL Server.
 
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name.
 
Example
 
 
But 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.
 
Example