为什么 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 的实际执行计划。两者都使用这个大小！