MERGE not working when index created on temporal table.
Following are the steps to reproduce the MERGE statement issue in any SQL 2017 database.
Script is attached for all the steps.
--Steps to reproduce fail Scenario
- Create table in SQL Server 2017 database with named system versioned temporal/history table.
- Create non-clustered index on the temporal/history table.
- Insert 400+ records in table.
- Execute the merge statement (source table should have data for both Insert and Update scenario), and it will fail with following error
Msg 681, Level 16, State 3, Line 453
Attempting to set a non-NULL-able column's value to NULL.
--Steps to see success scenario
- Drop the index from history table
- Execute the merge statement and it will succeed.
Kanan Osmanli commented
any possible workaround except dropping non-clustered index?
Still an issue on:
Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: )
Ian Claxton commented
This is also an issue on AWS RDS Microsoft SQL Server Standard (64-bit) v13.0.4422.0
FYI...I changed the index from RECORD_ROWVERSION (rowversion datatype) to COL_1_ID and it then worked.
Looks like issue with the ways MERGE is written.
Is you join source and target tables on primary key of source table it won't be an issue.
Same for 2016 13.0.4224.16 on multiple servers.