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.