10

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
CSharpFiasco
  • 204
  • 3
  • 8
  • 2
    Obligatory: [So, you want to use MERGE, eh?](https://sqlblog.org/merge) – Aaron Bertrand Jan 16 '22 at 20:55
  • 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)`. – Thom A Jan 16 '22 at 21:10
  • 1
    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). – Aaron Bertrand Jan 16 '22 at 21:10
  • `HOLDLOCK` had no affect when I gave it go, @AaronBertrand :[db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=fef299fa9d6e169ad818dccf3c36c8ec) – Thom A Jan 16 '22 at 21:11
  • 1
    @Larnu well it should always be there in any case. :-) – Aaron Bertrand Jan 16 '22 at 21:13
  • That I don't disagree with. :) – Thom A Jan 16 '22 at 21:14
  • Some [information about wide plans](https://dba.stackexchange.com/a/30347/1186) 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](https://www.sql.kiwi/2013/01/optimizing-t-sql-queries-that-change-data.html). – Aaron Bertrand Jan 16 '22 at 21:18
  • 2
    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. – CSharpFiasco Jan 16 '22 at 22:55
  • 1
    ..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;` – lptr Jan 16 '22 at 23:56
  • 1
    I'm also very interested to get an answer. This week we stumbled again over this. Just for reference: https://github.com/dotnet/efcore/issues/22852 – Daniel C. Jun 15 '22 at 06:56
  • https://feedback.azure.com/d365community/idea/e17ede5d-5b25-ec11-b6e6-000d3a4f0da0 – Daniel C. Jun 15 '22 at 07:16
  • 1
    Also directly related to the issue seems to be https://social.technet.microsoft.com/Forums/en-US/f4fbdfcc-c10e-4412-8011-2bbd3cdda5eb/merge-on-temporal-table-fails-with-attempting-to-set-a-nonnullable-columns-value-to-null – AHiggins Oct 24 '22 at 11:50
  • This issue persists in SQL2019 CU20. Why are Microsoft so useless? – Ian Kemp May 31 '23 at 17:11

0 Answers0