Comparing NULL values in SQL Server

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.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

2 Responses to Comparing NULL values in SQL Server

  1. Nitin says:

    Good Stuff Munish….!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s