The ANSI_NULLS used
to determine that how SQL Server handles the comparison operations with NULL
values using = or <> operator.
Syntax: SET ANSI_NULLS ON/OFF
1) SET
ANSI_NULLS ON:
This option always
return FALSE when comparison with NULL using = or <> operator. So
to do the comparison with NULL values we need to use IS NULL and IS
NOT NULL.
2) SET
ANSI_NULLS OFF:
Using this option,
we can compare NULL using = and <> and will work as usual.
NOTE: The default
option is ON.
Examples:
