SQL Server 2008R2: SET ANSI_NULLS OFF does not affect merge matching with null values

Question

I have read up on the use of "SET ANSI_NULLS OFF" for a current session to be able to evaluate NULL = NULL to true, e.g. the following example shows the different between ANSI_NULLS ON AND ANSI_NULLS OFF:

QUERY A:

SET ANSI_NULLS OFF

IF(NULL = NULL)
    SELECT 'NULL = NULL'
ELSE 
    SELECT 'NO MATCH'

RESULT: 'NULL = NULL'

QUERY B:

SET ANSI_NULLS ON

IF(NULL = NULL)
    SELECT 'NULL = NULL'
ELSE 
    SELECT 'NO MATCH'

RESULT: 'NO MATCH'

So this shows the difference between the ON and OFF setting.

This also seems to work when using it in a where clause in a standard select statement.

HOWEVER, this does not seem to work in a merge when source and target fields are null.

Reproducing a simple scenario:

CREATE TEST TABLE:

CREATE TABLE [dbo].[TestTable]
(
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [SomeText] [NVARCHAR](100) NULL,
    [Counter] [INT] NOT NULL,

    CONSTRAINT [PK_TestTable] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

MERGE QUERY

MERGE INTO TestTable AS Target
USING (VALUES(NULL)) AS Source(SomeText) ON Target.SomeText = Source.SomeText

WHEN MATCHED THEN
    UPDATE SET Target.Counter = Target.Counter + 1

WHEN NOT MATCHED THEN
    INSERT (SomeText) VALUES(Source.SomeText);

If matched, the counter is increased by 1. If not, a new row is inserted. When running the query twice, the result is two rows, which is not what I would expect when ansi_nulls are off.

If I change the value NULL til 'test', the match works just fine, e.g.

USING (VALUES(NULL)) => USING (VALUES('test'))

Is there some special behaviour going on when using merges that explains this? Or is it a bug in sql server?

NOTE: I am not looking for a work-around using an ISNULL(...)-solution or something like that. This way I cannot ensure effective use of an index of the matching fields. The original issue is regarding a merge with multiple match fields where multiple of these can happen to be null.

Answer 1

SET ANSI_NULLS only affects the semantics of NULL comparisons in very limited cases. Specifically it

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. (source)

When you wrap the literal NULL in a derived table this condition is no longer met so it is not expected that this setting will do as you want.

Answer 2

Workaround only!

If you want to handle nulls you could change ON condition from Target.SomeText = Source.SomeText to IS NOT DISTINCT FROM equivalent:

MERGE INTO TestTable AS Target
USING (VALUES(NULL)) AS Source(SomeText)
ON EXISTS (SELECT Target.SomeText INTERSECT SELECT Source.SomeText)
WHEN MATCHED THEN
UPDATE SET Target.Counter = ISNULL(Target.Counter,1) + 1
WHEN NOT MATCHED THEN
INSERT (SomeText) VALUES(Source.SomeText);

db<>fiddle demo

I agree with comments that you should avoid using SET ANSI_NULLS OFF because it is deprecated.