Resolve MERGE Statement issue on CDC Enabled Table

In this blog, I will explain the steps to be followed for resolving the issue tthat logs MERGE statement as DELETE followed by INSERT instead of UPDATE on a 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:

USE[EmployeeeDB]

CREATETABLE[dbo].[Table1]
(
[AppraisalNumber][nvarchar](40)NOTNULL
)
CREATEUNIQUENONCLUSTEREDINDEX[NonClusteredIndex-20160303-155449]ON[dbo].[Table1]
(
[AppraisalNumber]ASC
)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)
GO

Enable CDC on EmployeeDB using:

EXECsys.sp_cdc_enable_db;

Enable CDC on Table1 using:

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:



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:



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

Open SQL Server configuration Manager:



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



Now, execute the same MERGE statement and check CDC 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.