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
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
