Why does a merge into a temporal table with a nonclustered index in the history table throw an error

Question

I get the following error when I try to merge under a few conditions. Is someone able to explain why? Seems like it's a problem with SQL Server itself, but I wanted to post it here to confirm.

Attempting to set a non-NULL-able column's value to NULL.

1. The target table must be versioned
2. The history table must have a non-clustered index
3. Sufficient records must be inserted. In the example below, merging 2731 records fails, but 2730 merges in just fine

I am using SQL Server 2017 but have also observed it in Azure SQL

BEGIN TRANSACTION
SET XACT_ABORT ON;

CREATE TABLE RandomNumberHistory (Id INT NOT NULL, Number INT NOT NULL, [ValidFrom] DATETIME2 NOT NULL, [ValidTo] DATETIME2 NOT NULL);
CREATE TABLE RandomNumber (Id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY, Number INT NOT NULL, 
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START, 
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END, 
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.RandomNumberHistory));

/* Problematic index */
CREATE NONCLUSTERED INDEX [IX_RandomNumberHistory] ON RandomNumberHistory(Number);

CREATE TABLE #MergeTable (Number INT NOT NULL);

;WITH CTE AS
(
    SELECT ABS( CAST( CAST( NEWID() AS VARBINARY) AS INT ) ) % 100000 AS RandomNumber, 1 AS Counter
    UNION ALL
    SELECT ABS( CAST( CAST( NEWID() AS VARBINARY) AS INT ) ) % 100000 AS RandomNumber, Counter + 1 AS Counter
    FROM CTE
    WHERE CTE.Counter < 50000 /* Seems to fail starting at 2731 records */
)
INSERT INTO #MergeTable (CTE.Number)
SELECT RandomNumber
FROM CTE
OPTION (MAXRECURSION 0);

MERGE RandomNumber AS Target
USING (
    SELECT Number
    FROM #MergeTable
) AS Source
ON Target.Number = Source.Number
WHEN NOT MATCHED BY TARGET
THEN INSERT (Number) VALUES (Source.Number)
WHEN MATCHED THEN DELETE;
;

ROLLBACK TRANSACTION

Blurb

Obligatory: So, you want to use MERGE, eh? – Cullie

FYI, the columns listed after the target table in an INSERT is the columns in the target table. You have INSERT INTO #MergeTable (CTE.Number). – Allwein

I do wonder if HOLDLOCK on the MERGE would help, or if 2,371 rows happens to be the thing that pushes you to a wide update plan. But honestly I would just avoid the MERGE altogether and write it as two statements (in case the link above doesn't make that clear). – Cullie

HOLDLOCK had no affect when I gave it go, @AaronBertrand :db<>fiddle – Allwein

@Larnu well it should always be there in any case. :-) – Cullie

That I don't disagree with. :) – Allwein

Some information about wide plans and potential issues when a non-clustered index is involved (but not directly addressing temporal or MERGE specifically), and a more elaborate treatment of the subject here. – Cullie

My question isn't necessarily to find a work around, but to start a conversation about this particular issue. It seems to be in several versions of SQL Server and the issue is not tracked very well. I've had to refactor the merge statement into inserts and deletes, and so I'm hoping that asking the community will save someone similar trouble. – Blurb

..the deleted.* of merge outputs nullable columns for non-nullable ones and this most likely “conflicts” with the index on the non-nullable column. Try with a filtered index CREATE NONCLUSTERED INDEX [IX_RandomNumberHistory] ON RandomNumberHistory (Number) where number is not null; – Tingly

I'm also very interested to get an answer. This week we stumbled again over this. Just for reference: github.com/dotnet/efcore/issues/22852 – Phlegmy

feedback.azure.com/d365community/idea/… – Phlegmy

Also directly related to the issue seems to be social.technet.microsoft.com/Forums/en-US/… – Barone

This issue persists in SQL2019 CU20. Why are Microsoft so useless? – Haarlem

Still an issue in Microsoft SQL Server 2022 (RTM-GDR) (KB5032968) - 16.0.1110.1 (X64) – Diameter

Answer 1

So interestingly enough, I am also getting this same error in SQL Server 2019. For simplicity sake, the temporal table I am performing a MERGE on is similar in structure to the example given by hadi ranjdoust in a previous answer, there are just more data columns.

The original source of my merge was a CTE, after encountering this error, I created a temporary table with the same structure/nullability as my target table. Populated this temporary table with the data from the CTE, and then used the temporary table as the source for my MERGE. Doing this seems to have resolved my error.

Poucher

We tried this as well but it did not seem to help – Charmainecharmane

Answer 2

edit create table to :

CREATE TABLE RandomNumber (Id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1), Number INT NOT NULL, 
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START, 
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END, 
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.RandomNumberHistory));

Mandeville

edit "Id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY" to : "Id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)" – Mandeville

Thank you for giving this a try. This did not work testing on SQL Server 2019 nor on 2022. What version did you try this on? – Blurb