ANSI_NULLS

NULL comparison conditions are different from others. Because NULL means UNKNOWN, there are three conditions when using NULL:

TRUE

FALSE

UNKNOWN

This is because a value that is unknown cannot be compared logically against any other value.

T-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF.

In some cases we need it very much for example in complex stored procedures or triggers. These tow query results are the same:

SELECT *
     FROM tbluser
        WHERE notes IS NULL ;
SET ANSI_NULLS OFF
go

SELECT *
     FROM tbluser
        WHERE notes = NULL ;
go
Advertisements
This entry was posted in Joy of T-SQL, T-SQL Tips & Tricks. Bookmark the permalink.

Leave a comment

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