Why MERGE doesn't insert more than 277 records into a table which is configured with temporal table and a non-clustered index on history table

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 NONCLUSTE

Azure SQL Database sometimes builds an invalid execution plan for your merge-insert.

When it decides to maintain the column store history table using a single operator (a narrow plan) everything is fine. This trivially includes the case where the history table has no secondary indexes.

When it decides to maintain the history table using separate operators for the base table and secondary indexes (a wide plan) things go wrong when using the OUTPUT INTO option. The choice of plan is sensitive to cardinality estimates.

For example, the plan for OUTPUT only (not writing to a table variable) includes a table spool. The spool saves the rows before the Filter that removes any rows from the stream where ValidTo is null. The spool then replays the (unfiltered) rows for return to the client:

When OUTPUT INTO is used, the same stream is used for both maintaining the secondary index on the history table and providing rows for the output table. This creates a problem because a pure insert results in no rows added to the history, so all rows are filtered out.

A column store index is not required on the history table for this problem to manifest.

This is a product defect you should report directly to Microsoft Support by creating a support request in the Azure Portal.

Side note: rows arriving at the history table Index Insert are not actually inserted because the action column tells it not to. These details aren't exposed in showplan unfortunately. A possible fix would be to test the action as well as ValidTo in the filter.

The extra Filter does not appear on SQL Server 2019 CU16-GDR:

This feels like a bug fix for implied nullability problems that has been applied to Azure SQL Database before the box product. If so, it is a little surprising it does not react to QO compatibility level hints.

Daniel C.:

Microsoft confirmed this as a bug and also rolled-out a quick fix for one of our affected databases. I can confirm that this fix is resolving my issue.

StackExchange Database Administrators Q#313569, answer score: 27