SQL Server 2008R2:SET ANSI_NULLS OFF不影响具有null值的合并匹配

Question

我已经阅读了关于当前会话使用"SET ANSI_NULLS OFF"的信息，以便能够将NULL=NULL计算为true，例如，以下示例显示了ANSI_NULL on和ANSI_NULLS OFF之间的差异：

查询A:

SET ANSI_NULLS OFF
IF(NULL = NULL)
SELECT 'NULL = NULL'
ELSE 
SELECT 'NO MATCH'

结果：'NULL=NULL'

查询B:

SET ANSI_NULLS ON
IF(NULL = NULL)
SELECT 'NULL = NULL'
ELSE 
SELECT 'NO MATCH'

结果："不匹配">

因此，这显示了ON和OFF设置之间的差异。

在标准select语句的where子句中使用它时，这似乎也起作用。

然而，当源字段和目标字段为null时，这在合并中似乎不起作用。

再现一个简单的场景：

创建测试表：

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 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关闭时所期望的

如果在"test"之前将值更改为NULL，则匹配效果良好，例如

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

在使用合并时是否有一些特殊的行为可以解释这一点？或者这是sql server中的一个错误？

注意：我不是在寻找使用ISNULL(…)解决方案或类似的解决方案。通过这种方式，我无法确保有效使用匹配字段的索引。最初的问题是关于具有多个匹配字段的合并，其中多个字段可能恰好为空。

Answer 1

SET ANSI_NULLS仅在非常有限的情况下影响NULL比较的语义。特别是

仅当比较的操作数之一为变量为NULL或文字为NULL。如果比较是列或复合表达式，设置不影响比较。(来源)

在派生表中包装文字NULL时，不再满足此条件，因此不希望此设置按您的意愿执行。

Answer 2

仅限变通方法！

如果您想处理null，您可以将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);

db<>小提琴演示

我同意您应该避免使用SET ANSI_NULLS OFF的意见，因为它已被弃用。