Azure SQL Server中含HASHBYTES的MERGE遇源表重复数据为何不报错？

在Azure SQL Server中使用MERGE语句时遇到以下现象：

• 当语句不含HASHBYTES条件时，源表的重复数据会触发预期错误：

“MERGE语句尝试多次更新或删除同一行。这是因为目标行匹配了多个源行。MERGE语句无法多次更新/删除目标表的同一行。请优化ON子句确保目标行最多匹配一个源行，或使用GROUP BY子句对源行分组。”

• 加入HASHBYTES条件后，即使源表存在重复数据，多次执行MERGE也不会报错。

测试代码如下：

drop table if exists #source
drop table if exists #target

CREATE TABLE #target(id int IDENTITY(1,1) NOT NULL,
  a int NOT NULL,
  b int NOT NULL,
  c varchar(20) NOT NULL,
  d decimal(10,3) NULL,
  PRIMARY KEY (id))

CREATE TABLE #source(a int NOT NULL,
  b int NOT NULL,
  c varchar(20) NOT NULL,
  d decimal(10,3) NULL)
INSERT #target (a, b, c, d) VALUES (1, 1, 'TEXT', NULL)
INSERT #source (a, b, c, d) VALUES (2, 2, 'TEST1', NULL), (2, 2, 'TEST2', NULL)
go

select a, b, count(*) as NumberOfRows
from #source
group by a, b
having count(*)>1

MERGE #target t
USING #source s ON t.a = s.a AND t.b = s.b
WHEN NOT MATCHED BY TARGET THEN
   INSERT (a, b, c, d) VALUES(s.a, s.b, s.c, s.d)
WHEN MATCHED and hashbytes('SHA2_512', CONCAT(t.c, t.d)) != hashbytes('SHA2_512', CONCAT(s.c, s.d))
THEN UPDATE
   set t.c=s.c
   , t.d=s.d 
;

select * from #target

原因分析 #

首次执行MERGE时，源表中(2,2)的两行数据会被插入到目标表，新增id=2（对应TEST1）和id=3（对应TEST2）两行记录。

再次执行MERGE时：

1. 针对目标表id=2行（初始c='TEST1'）：
   • 匹配源表第一行(2,2,'TEST1')时，HASHBYTES计算结果相等，不触发UPDATE；
   • 匹配源表第二行(2,2,'TEST2')时，HASHBYTES结果不等，触发UPDATE将c改为TEST2。此时该目标行的c值已更新，后续不再满足HASHBYTES不等的条件，不会被再次更新。
2. 针对目标表id=3行（初始c='TEST2'）：
   • 匹配源表第一行(2,2,'TEST1')时，HASHBYTES结果不等，触发UPDATE将c改为TEST1；
   • 匹配源表第二行(2,2,'TEST2')时，HASHBYTES结果相等，不触发UPDATE。

MERGE的错误触发条件是同一目标行在单次执行中被多次UPDATE/DELETE，而加入HASHBYTES后，同一目标行在单次MERGE里只会被更新一次——第一次更新后目标行的字段值变化，后续匹配的源行不再满足更新条件，因此不会触发重复更新的错误。

如果去掉HASHBYTES条件，只要a和b匹配就执行UPDATE，同一目标行会被源表的两行数据连续触发两次UPDATE，直接违反MERGE的规则，因此会报错。

内容的提问来源于stack exchange，提问作者Дрвосеча