通過游標更新具有 INSTEAD OF UPDATE 觸發器的視圖時，SQL Server 使用索引掃描而不是查找

我有一個第 3 方應用程序，它使用一個游標來更新我的 SQL Server 2014 表中的行。我無法修改此程式碼，因此設計了一個方案，將游標指向一個視圖，該視圖具有一個INSTEAD OF UPDATE觸發器，我可以在其中攔截並完全控制更新。我很清楚游標是邪惡的，但我不得不使用它們，因為我無法修改源程序。

當UPDATE…WHERE CURRENT OF語句執行時，它執行的是聚集索引掃描，而不是尋找游標目前指向的記錄。我無法確定為什麼優化器在可以進行搜尋時進行掃描。我相信這是導致問題的游標+視圖+而不是更新觸發器的組合，因為如果我從我的測試中刪除這三個變數中的任何一個，索引搜尋就會正確使用。

請注意，表中有多少行並不重要；優化器總是根據執行計劃使用掃描。我什至刪除了觸發器中的所有邏輯以進一步簡化測試。

這是一些重新創建問題的簡單程式碼：

CREATE TABLE [dbo].[Person](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](40) NULL,
 CONSTRAINT [PK_Person_Id] PRIMARY KEY CLUSTERED ( [Id] ASC )
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
 ON [PRIMARY]
GO

CREATE VIEW [dbo].[vPerson]
AS SELECT Id, Name FROM dbo.Person
GO

CREATE TRIGGER [dbo].[InsteadOfUpdate_vPerson] ON [dbo].[vPerson]
INSTEAD OF UPDATE
AS
BEGIN
 SET NOCOUNT ON
END
GO

INSERT INTO Person VALUES('John Doe');
GO

--This will use a seek
UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE Id = 1
GO

--This will not use a seek
DECLARE c CURSOR FOR
SELECT * FROM dbo.vPerson
WHERE Id = 1
FOR UPDATE

OPEN c
FETCH NEXT FROM c

UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE CURRENT OF c
CLOSE c
DEALLOCATE c
GO

任何有關嘗試的幫助或建議將不勝感激。

這看起來像是一個疏忽。

WHERE CURRENT OF當使用執行更新並且目標視圖（模式綁定與否）具有 T-SQL 而不是更新觸發器時，優化器無法生成應用樣式索引循環連接，無論行數或任何其他考慮因素如何：

這顯示了一個表中有近 20,000 行的範例（從 AdventureWorks 的 Person 表中複製，碰巧）。

連接謂詞“卡”在嵌套循環連接運算符本身上，而不是被推入內側以產生查找：

由於您無法更改程式碼，因此您應該通過正常的 Microsoft 支持渠道**將此報告為錯誤。**您也可以在 Connect 上報告錯誤，但通過該路線獲得快速響應或修復的機會要低得多。

---

只是出於興趣，您所追求的計劃可以使用 API 游標定位更新（內部最類似的操作）：

DECLARE 
   @cur integer,
   @scrollopt integer = 2 | 8192 | 32768 | 131072, -- DYNAMIC | AUTO_FETCH | CHECK_ACCEPTED_TYPES | DYNAMIC_ACCEPTABLE
   @ccopt integer = 2 | 32768 | 131072, -- SCROLL_LOCKS | CHECK_ACCEPTED_OPTS | SCROLL_LOCKS_ACCEPTABLE
   @rowcount integer = 1;

-- Open the cursor
EXECUTE sys.sp_cursoropen
   @cur OUTPUT,
   N'
   SELECT * FROM dbo.vPerson WHERE Id = 1;
   ',
   @scrollopt OUTPUT,
   @ccopt OUTPUT,
   @rowcount OUTPUT;

-- Request a positioned update
EXECUTE sys.sp_cursor
   @cur,
   1, -- UPDATE
   1, -- row number in buffer
   'dbo.vPerson', -- table (unambiguous in this case)
   'Name=''Banana'''; -- new value

-- Close    
EXECUTE sys.sp_cursorclose -1;

執行計劃是：

注意 Person 上的索引搜尋（謂詞不是“卡住”）：

這不是您的解決方法，因為您無法更改源查詢。沒有辦法提示或計劃指導您解決問題；優化器根本無法生成您在特定情況下期望的搜尋計劃。嘗試例如FORCESEEK提示只會導致錯誤消息表明優化器無法生成執行計劃。

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