SET ANSI_NULLS ON/OFF

It specify the behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.

When SET ANSI_NULLS is ON then comparison operator(=,<>) doesn’t work with NULL values, in that case to work with NULL values we have to use “IS NULL” or “IS NOT NULL”.

When SET ANSI_NULLS is OFF then comparison operator(=,<>) work with NULL values.