為什麼這個 MERGE 語句會導致會話被終止？

我有以下MERGE針對數據庫發出的聲明：

MERGE "MySchema"."Point" AS t
USING (
      SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
        FROM @p1 AS d
        JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
   LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
      ) AS s
  ON s."ObjectId" = t."ObjectId"
WHEN NOT MATCHED BY TARGET 
   THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
WHEN MATCHED 
   THEN UPDATE 
    SET "Name" = s."PointName"
      , "LocationId" = s."LocationId"
      , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

但是，這會導致會話終止並出現以下錯誤：

消息 0，級別 11，狀態 0，第 67 行 目前命令發生嚴重錯誤。結果，如果有的話，應該丟棄。

消息 0，級別 20，狀態 0，第 67 行 目前命令發生嚴重錯誤。結果，如果有的話，應該丟棄。

我已經將一個簡短的測試腳本放在一起，它會產生錯誤：

USE master;
GO
IF DB_ID('TEST') IS NOT NULL
DROP DATABASE "TEST";
GO
CREATE DATABASE "TEST";
GO
USE "TEST";
GO

SET NOCOUNT ON;

IF SCHEMA_ID('MySchema') IS NULL
EXECUTE('CREATE SCHEMA "MySchema"');
GO

IF OBJECT_ID('MySchema.Region', 'U') IS NULL
CREATE TABLE "MySchema"."Region" (
"Id" TINYINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Region" PRIMARY KEY,
"Name" VARCHAR(8) NOT NULL CONSTRAINT "UK_MySchema_Region" UNIQUE
);
GO

INSERT [MySchema].[Region] ([Name]) 
VALUES (N'A'), (N'B'), (N'C'), (N'D'), (N'E'), ( N'F'), (N'G');

IF OBJECT_ID('MySchema.Location', 'U') IS NULL
CREATE TABLE "MySchema"."Location" (
"Id" SMALLINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Location" PRIMARY KEY,
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Location_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
"Name" VARCHAR(128) NOT NULL,
CONSTRAINT "UK_MySchema_Location" UNIQUE ("Region", "Name") 
);
GO

IF OBJECT_ID('MySchema.Point', 'U') IS NULL
CREATE TABLE "MySchema"."Point" (
"ObjectId" BIGINT NOT NULL CONSTRAINT "PK_MySchema_Point" PRIMARY KEY,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL CONSTRAINT "FK_MySchema_Point_Location" FOREIGN KEY REFERENCES "MySchema"."Location"("Id"),
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Point_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
CONSTRAINT "UK_MySchema_Point" UNIQUE ("Name", "Region", "LocationId")
);
GO

-- CONTAINS HISTORIC Point DATA
IF OBJECT_ID('MySchema.PointHistory', 'U') IS NULL
CREATE TABLE "MySchema"."PointHistory" (
"Id" BIGINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_PointHistory" PRIMARY KEY,
"ObjectId" BIGINT NOT NULL,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL,
"Region" TINYINT NOT NULL
);
GO

CREATE TYPE "MySchema"."PointTable" AS TABLE (
"ObjectId"      BIGINT          NOT NULL PRIMARY KEY,
"PointName"     VARCHAR(64)     NOT NULL,
"Location"      VARCHAR(16)     NULL,
"Region"        VARCHAR(8)      NOT NULL,
UNIQUE ("PointName", "Region", "Location")
);
GO

DECLARE @p1 "MySchema"."PointTable";

insert into @p1 values(10001769996,N'ABCDEFGH',N'N/A',N'E')

MERGE "MySchema"."Point" AS t
USING (
      SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
        FROM @p1 AS d
        JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
   LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
      ) AS s
  ON s."ObjectId" = t."ObjectId"
WHEN NOT MATCHED BY TARGET 
   THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
WHEN MATCHED 
   THEN UPDATE 
    SET "Name" = s."PointName"
      , "LocationId" = s."LocationId"
      , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

如果我刪除該OUTPUT子句，則不會發生錯誤。此外，如果我刪除deleted引用，則不會發生錯誤。所以我查看了 MSDN 文件中的OUTPUT條款：

DELETED 不能與 INSERT 語句中的 OUTPUT 子句一起使用。

這對我來說是有道理的，但重點MERGE是你可能事先不知道。

此外，無論採取何種操作，以下腳本都可以正常工作：

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10), 
    CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10), 
    CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
   THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
   THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
   THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO 

此外，我還有其他查詢以OUTPUT與引發錯誤的查詢相同的方式使用，並且它們工作得非常好——它們之間的唯一區別是參與MERGE.

這給我們的生產帶來了重大問題。我已經在具有 128GB RAM、12 x 2.2GHZ 核心、Windows Server 2012 R2 的 VM 和物理上重現了 SQL2014 和 SQL2016 中的此錯誤。

從查詢生成的估計執行計劃可以在這裡找到：

預計執行計劃

這是一個錯誤。

它與MERGE用於避免顯式萬聖節保護和消除連接的特定孔填充優化有關，以及它們如何與其他更新計劃功能互動。

我的文章《萬聖節問題 - 第 3 部分》中有關於這些優化的詳細資訊。

贈品是插入，然後是同一張表上的合併：

解決方法

有幾種方法可以打敗這種優化，從而避免這個錯誤。

1. 使用未記錄的跟踪標誌來強制明確的萬聖節保護：

OPTION (QUERYTRACEON 8692);

2. ON將子句更改為：

ON s."ObjectId" = t."ObjectId" + 0

3. 更改表類型PointTable以將主鍵替換為：

ObjectID bigint NULL UNIQUE CLUSTERED CHECK (ObjectId IS NOT NULL)

約束部分是可選的CHECK，包括在內以保留主鍵的原始拒絕空屬性。

“簡單”的更新查詢處理（外鍵檢查、唯一索引維護和輸出列）已經足夠複雜了。使用MERGE添加了幾個額外的層。將其與上面提到的特定優化結合起來，您就有了一個很好的方法來遇到這樣的邊緣情況錯誤。

還有一個添加到已報告的一長串錯誤MERGE中。

引用自：https://dba.stackexchange.com/questions/140880