Resolve MERGE Statement issue on CDC Enabled Table

This will happen only when the table has a unique clustered\non-clustered index on it. CDC[Change Data Capture] is a tool to handle change tracking on SQL Server 2008 or above, it logs all inserts\updates\deletes on a table. When you fire a MERGE statement that will update a record, it will log the change in CDC table as DELETE followed by INSERT. The expected result is an UPDATE. Let’s understand it with an example.

Create a table with unique nonclustered index using below script:

  1. USE[EmployeeeDB]  
  2.   
  3. CREATETABLE[dbo].[Table1]  
  4. (  
  5. [AppraisalNumber][nvarchar](40)NOTNULL  
  6. )  
  7. CREATEUNIQUENONCLUSTEREDINDEX[NonClusteredIndex-20160303-155449]ON[dbo].[Table1]  
  8. (  
  9. [AppraisalNumber]ASC  
  10. )WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=ON,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=ON,DROP_EXISTING=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=OFF,ALLOW_PAGE_LOCKS=OFF)  
  11. GO  
  12.   
  13. Enable CDC on EmployeeDB using:  
  14.   
  15. EXECsys.sp_cdc_enable_db;  
Enable CDC on Table1 using:
  1. EXECsys.sp_cdc_enable_table@source_schema=N'dbo',@source_name='Table1',@role_name=N'cdc_admin'  
Execute below MERGE statement to do an update on Table1:

code

If you check CDC table, it will show DELETE [__$operation as 1] followed by INSERT [__$operation as 2] for a UPDATE operation using MERGE statement:

statement

To resolve it, we need to add startup parameter -T8207 to SQL Server instance by following below steps:

Open SQL Server configuration Manager:

config

Select your instance and go to properties -> Startup parameters tab -> Add -T8207 than click Add and restart the instance.

properties

Now, execute the same MERGE statement and check CDC table:

table

We will see Update before [__$operation=3] and Update After[__$operation=4]. For more details, please refer here.

I am ending things here, I hope this article will be helpful for all.