Sometime we want to check for the values by comparing them with the NULL identifier.
Like: DECLARE @str1 CHAR, @str2 CHAR
SET @str1 = NULL
SET @str2 = NULL
IF @str1 = @str2 SELECT ‘NULL Values Are Equal.’
ELSE SELECT ‘NULL Values Are NOT Equal.’
Result would be: NULL Values Are NOT Equal.
This is because by default SET ANSI_NULLS option is ON which would evaluate every value to UNKNOWN/NULL compared with NULL values.
So to get the feature to compare NULL Values, make SET ANSI_NULLS as OFF , fire the query and after using it make it to ON using: SET ANSI_NULLS OFF
Now for the same query result would be : ‘NULL Values Are Equal.
Note: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.