SQL Server 2008R2: SET ANSI_NULLS OFF не влияет на сопоставление слиянием с нулевыми значениями

Question

Я прочитал об использовании "SET ANSI_NULLS OFF" для текущего сеанса, чтобы иметь возможность оценить NULL = NULL в true, например, в следующем примере показано различие между ANSI_NULLS ON и ANSI_NULLS OFF:

QUERY A:

SET ANSI_NULLS OFF

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

РЕЗУЛЬТАТ: 'NULL = NULL'

QUERY B:

SET ANSI_NULLS ON

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

РЕЗУЛЬТАТ: "НЕТ МАТЧ"

Таким образом, это показывает разницу между настройками ВКЛ и ВЫКЛ.

Это также работает при использовании его в предложении where в стандартном операторе select.

ОДНАКО, кажется, что это не работает в слиянии, когда поля источника и цели равны нулю.

Воспроизведение простого сценария:

СОЗДАЙТЕ ТАБЛИЦУ ТЕСТА:

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

Если сопоставлено, счетчик увеличивается на 1. Если нет, вставляется новая строка. При повторном выполнении запроса в результате получается две строки, что не соответствует ожиданиям, когда ansi_nulls выключены.

Если я изменю значение NULL до 'test', совпадение будет работать нормально, например

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

Существует ли какое-то особое поведение при использовании слияний, которое объясняет это? Или это ошибка в SQL Server?

ПРИМЕЧАНИЕ: я не ищу обходного пути, использующего ISNULL(...)- решение или что-то подобное. Таким образом, я не могу обеспечить эффективное использование индекса соответствующих полей. Первоначальная проблема касается слияния с несколькими полями совпадения, где несколько из них могут быть нулевыми.

Answer 1

SET ANSI_NULLS влияет только на семантику NULL сравнения в очень ограниченных случаях. Именно это

влияет на сравнение, только если один из операндов сравнения является либо переменной, равной NULL, либо литералом NULL. Если обе стороны сравнения являются столбцами или составными выражениями, настройка не влияет на сравнение. ( источник)

Когда вы заверните буквальное NULL в производной таблице это условие больше не выполняется, поэтому не ожидается, что этот параметр будет работать так, как вы хотите.

Answer 2

Только обходной путь!

Если вы хотите обрабатывать нули, вы можете изменить ON состояние от Target.SomeText = Source.SomeText в IS NOT DISTINCT FROM эквивалент:

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

ДБ <> Fiddle Demo

Я согласен с комментариями, что вы должны избегать использования SET ANSI_NULLS OFF потому что это устарело.