Daniel C.'s questions

Encontrei novamente um problema com o SQL Server e a instrução MERGE e preciso de alguma confirmação.

Posso reproduzir meu problema constantemente em um banco de dados do Azure (mas não em um SQL Server 2017/2019 local).

Por favor, execute os seguintes passos (passo a passo, não em uma execução de comando)!

1) Script para Schema:

    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) Script para inserir alguns dados aleatórios

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

Esta etapa deve retornar como resultado todas as linhas inseridas!

3) Excluir dados da Etapa 2) Esta etapa está preenchendo a tabela de histórico configurada

DELETE [dbo].[ImpactValue]

4) Insira novamente alguns dados aleatórios Você pode usar o script da etapa 2)

Devo observar que as etapas 1) - 4) devem ser executadas separadamente e não em arquivos GO.

Novamente esta etapa deve retornar como resultado todas as linhas inseridas! Mas não! Do meu lado sempre recebo um resultado vazio. Isso pode ser reproduzido em três de nossos bancos de dados de produção :(

A instrução MERGE é gerada pelo EF Core e atualmente estou resolvendo isso definindo um Max Batch Size. Mas esta não poderia ser a solução final.

Deve ter algo a ver com Tabelas Temporais com um índice não clusterizado configurado na tabela temporal.

Veja também:

No passado eu já tropecei neste problema:

• 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

Mas meu problema atual só pode ser reproduzido em um Banco de Dados SQL do Azure e não gera nenhum erro.

Notas laterais interessantes:

1. Se eu desabilitar temporariamente a tabela temporal -> está funcionando
2. Se eu excluir o índice não clusterizado [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId] -> está funcionando
3. Se eu usar SELECT TOP (@BatchSize) na etapa 2) --> está funcionando
4. Se eu usar apenas OUTPUT em vez de OUTPUT INTO @inserted0 --> está funcionando

Sem o índice COLUMNSTORE na tabela de histórico está funcionando. Ao remover apenas o índice COLUMNSTORE na tabela principal, vejo o mesmo problema.

Plano de execução real para um caso que (a) reproduz o problema com TOP 278e (b) não reproduz TOP (@BatchSize)disponível em https://1drv.ms/u/s!AsOa6e9ukBWQlIRg9_9eySDFp5hvEA?e=KBQBsP . Também adicionei os planos de execução reais para o tamanho do lote de 277. Ambos estão trabalhando com esse tamanho!