ANSI NULLS Behavioral Difference - Variables vs Literals

Impetus
The impetus for writing this blog comes from a recent interesting discussion that happened in one of the groups.
The topic of discussion was regarding ANSI NULL setting behavior. There is a difference in the way ANSI NULLS setting works while dealing with variables as well as with literal values. This blog illustrates on this difference in behavior as shown by ANSI NULLS

Ilustration

Consider the below illustration

For this example lets consider a table as below



declare @t table
(
ID int IDENTITY(1,1),
Val varchar(100)
)

Now lets populate the table with some values

INSERT @t(Val)
VALUES (NULL),('Test Val'),('foo'),('foobar')

Not lets see how ANSI NULL works in the case of a variable comparison

SET ANSI_NULLS OFF
GO

declare @searchword varchar(10) = 'test'

SELECT *
FROM @t
WHERE Val != @searchword

The result will be as below

As you see it will also consider NULL as a value and will return it among resultset as we expect.

Now lets see how it behaves while passing a value as a literal

Taking the example of the same table using a query as below

SELECT *

FROM @t

WHERE Val != 'test'

We will get the output as below

Compare this output with what we got earlier and you can see that it ignores the record with NULL value even when the ANSI NULLS is set to off. This would be different from how we expect ANSI NULLS to work.

Explanation

The explanation given by Microsoft for this discrepancy is that this behavior is by design.
This is documented in their official documentation as below

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

Many thanks to Adam Mechanic for sharing this useful piece of information.

This will clear the ambiguity around functionality of ANSI NULLS while dealing with variables and literals


References

ANSI NULLS