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