title: Why does a merge into a temporal table with a nonclustered index in the history table throw an error
body: <p>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.</p>
  <blockquote>
  <p>Attempting to set a non-NULL-able column's value to NULL.</p>
  </blockquote>
  <ol>
  <li>The target table must be versioned</li>
  <li>The history table must have a non-clustered index</li>
  <li>Sufficient records must be inserted. In the example below, merging 2731 records fails, but 2730 merges in just fine</li>
  </ol>
  <p>I am using SQL Server 2017 but have also observed it in Azure SQL</p>
  <pre><code>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 &lt; 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
  </code></pre>