為什麼 MERGE 不會將超過 277 條記錄插入配置有時態表和歷史表上的非聚集索引的表中

我再次發現 SQL Server 和 MERGE 語句存在問題，需要一些確認。

我可以在 Azure 數據庫上不斷重現我的問題（但不能在本地 SQL Server 2017/2019 上）。

請執行以下步驟（一步一步，而不是一個命令執行）！

1) 架構腳本：

   CREATE TABLE [dbo].[ImpactValueHistory]
   (
       [Rn] BIGINT NOT NULL,

       [ImpactId] UNIQUEIDENTIFIER NOT NULL,
       [ImpactValueTypeId] INT NOT NULL,

       [Date] DATE NOT NULL,
       [Value] DECIMAL(38, 10) NOT NULL,

       [ValidFrom] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
       [ValidTo] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),

       [ImpactPeriodId] INT NOT NULL,

       [NormalizedValue] DECIMAL(38, 10) NOT NULL,
   )
   GO

   CREATE CLUSTERED COLUMNSTORE INDEX [COLIX_ImpactValueHistory]
       ON [dbo].[ImpactValueHistory];
   GO

   CREATE NONCLUSTERED INDEX [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId]
       ON [dbo].[ImpactValueHistory] ([ValidFrom], [ValidTo], [ImpactId], [ImpactValueTypeId], [Date]);
   GO


   CREATE TABLE [dbo].[ImpactValue]
   (
       [Rn] BIGINT NOT NULL IDENTITY(1,1),

       [ImpactId] UNIQUEIDENTIFIER NOT NULL,
       [ImpactValueTypeId] INT NOT NULL,

       [Date] DATE NOT NULL,
       [Value] DECIMAL(38, 10) NOT NULL,

       [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT [DF_ImpactValue_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
       [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT [DF_ImpactValue_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),

       [ImpactPeriodId] INT NOT NULL,

       [NormalizedValue] DECIMAL(38, 10) NOT NULL,

       PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),

       CONSTRAINT [PK_ImpactValue] PRIMARY KEY NONCLUSTERED ([ImpactId], [ImpactValueTypeId], [Date], [ImpactPeriodId])
   )
   WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ImpactValueHistory]))
   GO

   CREATE UNIQUE CLUSTERED INDEX [IX_ImpactValue_Id] ON [dbo].[ImpactValue]([Rn])
   GO

   CREATE COLUMNSTORE INDEX [CIX_ImpactValue] ON [dbo].[ImpactValue] ([ImpactId], [ImpactValueTypeId], [Date], [Value], [NormalizedValue])
   GO

2) 插入一些隨機數據的腳本

DECLARE @inserted0 TABLE ([Date] DATE, [ImpactId] uniqueidentifier, [ImpactPeriodId] int, [ImpactValueTypeId] int);
MERGE [dbo].[ImpactValue] USING (
SELECT TOP 278 -- <-- this number is critical
       DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY [Name]) - 1, '2000-01-01') AS [Date],
       NEWID() AS [ImpactId], 
       1 AS [ImpactPeriodId], 
       1 AS [ImpactValueTypeId], 
       99 AS [Value], 
       99 AS [NormalizedValue]
   FROM [sys].[all_columns]
) AS i ([Date], [ImpactId], [ImpactPeriodId], [ImpactValueTypeId], [Value], [NormalizedValue]) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Date], [ImpactId], [ImpactPeriodId], [ImpactValueTypeId], [Value], [NormalizedValue])
VALUES (i.[Date], i.[ImpactId], i.[ImpactPeriodId], i.[ImpactValueTypeId], i.[Value], i.[NormalizedValue])
OUTPUT INSERTED.[Date], INSERTED.[ImpactId], INSERTED.[ImpactPeriodId], INSERTED.[ImpactValueTypeId]
INTO @inserted0;

SELECT * FROM @inserted0

這一步應該返回所有插入的行！

3）從步驟2中刪除數據） 這一步正在填充配置的歷史表

DELETE [dbo].[ImpactValue]

4）再次插入一些隨機數據 您可以使用步驟2中的腳本）

我必須注意，步驟 1) - 4) 應該單獨執行，而不是在GO.

這一步應該再次返回所有插入的行！但事實並非如此！ 在我這邊，我總是得到一個空的結果。這可以在我們的三個生產數據庫上重現:(

MERGE 語句由 EF Core 生成，目前我正在通過設置 Max Batch Size 來解決此問題。但這不可能是最終的解決方案。

它必須與在時態表上配置了非聚集索引的時態表有關。

也可以看看：

在過去，我已經偶然發現了這個問題：

• https://stackoverflow.com/questions/70734060/why-does-a-merge-into-a-temporal-table-with-a-nonclustered-index-in-the-history.
• https://github.com/dotnet/efcore/issues/22852

但是我目前的問題只能在 Azure SQL 數據庫上重現，並且不會引發任何錯誤。

有趣的旁注：

1. 如果我暫時禁用臨時表->它正在工作
2. 如果我刪除非聚集索引$$ IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId $$-> 它正在工作
3. 如果我在步驟 2 中使用 SELECT TOP (@BatchSize) -> 它正在工作
4. 如果我只使用 OUTPUT 而不是 OUTPUT INTO @inserted0 –> 它正在工作

如果沒有歷史表上的 COLUMNSTORE 索引，它就可以工作。通過僅刪除主表上的 COLUMNSTORE 索引，我看到了同樣的問題。

TOP 278(a) 重現問題和 (b) 不重現的情況的實際執行計劃TOP (@BatchSize)可在https://1drv.ms/u/s!AsOa6e9ukBWQlIRg9_9eySDFp5hvEA?e=KBQBsP獲得。我還添加了批量大小為 277 的實際執行計劃。兩者都使用這個大小！

Azure SQL 數據庫有時會為合併插入生成無效的執行計劃。

當它決定使用單個運算符（一個狹窄的計劃）維護列儲存歷史表時，一切都很好。這通常包括歷史表沒有二級索引的情況。

OUTPUT INTO當它決定對基表和二級索引（廣泛的計劃）使用單獨的運算符來維護歷史表時，使用該選項時會出錯。計劃的選擇對基數估計很敏感。

例如，OUTPUT僅計劃（不寫入表變數）包括表假離線。假離線保存過濾器之前的行，過濾器從流中刪除ValidTo為空的任何行。然後，假離線重播（未過濾的）行以返回給客戶端：

使用OUTPUT INTO時，相同的流用於維護歷史表的二級索引和為輸出表提供行。這會產生一個問題，因為純插入不會導致任何行添加到歷史記錄中，因此所有行都會被過濾掉。

歷史表上不需要列儲存索引來顯示此問題。

這是一個產品缺陷，您應該通過在 Azure 門戶中創建支持請求直接向 Microsoft 支持報告。

旁注：到達歷史表索引插入的行實際上並未插入，因為操作列告訴它不要插入。不幸的是，這些細節沒有在展示計劃中公開。一個可能的解決方法是在過濾器中測試操作以及ValidTo。

---

SQL Server 2019 CU16-GDR 上沒有出現額外的篩選器：

這感覺像是針對在盒裝產品之前已應用於 Azure SQL 數據庫的隱含可空性問題的錯誤修復。如果是這樣，它不會對 QO 兼容性級別提示做出反應，這有點令人驚訝。

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