I found again an issue with SQL Server and MERGE statement and need some confirmation.
I can reproduce my issue constantly on a Azure Database (but not on a on premise SQL Server 2017/2019).
Please execute following steps (step by step, not in one command execution)!
1) Script for 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 for inserting some random data
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
This step should return as a result all the inserted rows!
3) Delete data from Step 2) This step is filling up the configured history table
DELETE [dbo].[ImpactValue]
4) Insert again some random data You can use script from step 2)
I have to note, that steps 1) - 4) should be executed separately and not in GO.
Again this step should return as a result all the inserted rows! But it doesn't! On my side I always get an empty result. This is reproducible on three of our production databases :(
The MERGE statement is generated by EF Core and currently I'm workaround against this by setting a Max Batch Size. But this couldn't be the final solution.
It must have something to do with Temporal Tables with a configured non-clustered index on the temporal table.
See also:
In the past I already stumbled over this issue:
- 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
But my current issue is only reproducible on a Azure SQL Database and doesn't throw any error.
Interesting side notes:
- If I temporarily disable the temporal table -> it's then working
- If I delete the non-clustered index [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId] -> it's working
- If I use SELECT TOP (@BatchSize) in step 2) --> it's working
- If I use only OUTPUT instead of OUTPUT INTO @inserted0 --> it's working
Without the COLUMNSTORE index on the history table it is working. By only removing the COLUMNSTORE index on the main table I see the same issue.
Actual execution plan for a case that (a) repros the issue with TOP 278 and (b) doesn't repro with TOP (@BatchSize) available at https://1drv.ms/u/s!AsOa6e9ukBWQlIRg9_9eySDFp5hvEA?e=KBQBsP. I also added the actual execution plans for batch size of 277. Both are working with this size!
