How implement correctly MERGE for tables? Incorrect CDC result

Question

Once of a day one local SQL server doing synchronization with some linked server. The following query using for merge:

MERGE [TargetDb].[dbo].[TargetTable] AS Target
USING (SELECT * FROM [RemoteServer].[SourceDb].[dbo].[TestTable]) AS Source
    ON (Target.[ID] = Source.[ID])
WHEN MATCHED THEN
UPDATE SET  Target.[ID] = Source.[ID],
            Target.[TestFieldString] = Source.[TestFieldString],
            Target.[TestFieldInt] = Source.[TestFieldInt]
WHEN NOT MATCHED BY TARGET THEN
INSERT 
    (ID, TestFieldString, TestFieldInt) 
VALUES 
    (ID, Source.TestFieldString, Source.TestFieldInt);

Also on target server works CDC (change data capture). Looks at CDC statistic i found that transaction on update not register in CDC. Following query for statistics capture:

SELECT ch.*,  ch.__$seqval,
    CASE ch.__$operation
    WHEN 1 THEN 'delete'
    WHEN 2 THEN 'insert'
    WHEN 3 THEN 'prev val'
    ELSE 'new val' END  as operation,
    map.tran_begin_time, map.tran_end_time
FROM [cdc].[dbo_TestTable_CT] ch
    JOIN [cdc].[lsn_time_mapping] map
    ON ch.[__$start_lsn] = map.start_lsn
ORDER BY ch.__$start_lsn, ch.__$seqval

After several tests it became clear. This happens with tables that contains the primary key. If source table with PK contains changes then Merge query doing DELETE and then doing INSERT for each row.

Example of CDC is following:

Result of first merge in 1 row. Target table was empty before this operation.
Update forTestFieldString in source table and result for second merge in 2 and 3 rows.
Inserting new record in source table and executing merge. Result for third merge in 4,5,6 rows.

It is really strange. I waiting prev val and new val in operation column. Script for creating this table looks folowing:

CREATE TABLE [dbo].[TestTable](
    [ID] [uniqueidentifier] NOT NULL PRIMARY KEY,
    [TestFieldString] [varchar](50) NULL,
    [TestFieldInt] [int] NULL
) ON [PRIMARY]
GO

Folowing CDC result for similar table without primary key.

Result of first merge in 1 row. Target table was empty before this operation.
Updating forTestFieldString in source table and merging. Result in 2 and 3 rows.
Inserting new record in source table and merging. Result in 4 row.

It is correct CDC result for following table:

CREATE TABLE [dbo].[TestTable](
    [ID] [uniqueidentifier],
    [TestFieldString] [varchar](50) NULL,
    [TestFieldInt] [int] NULL
) ON [PRIMARY]
GO

I read on MSD about MERGE but don't find description for this issue.

How fix it and what is wrong? Maybe it is bug or feature of MERGE clause or CDC functionality? It is a specifically issue, but for the functionality that I'm developing, it is a very critical issue.

Also, before merge I was attempted to disabled constraints in table but got an error that say CDC was locked the constraints. Maybe there is possible mark the constraints (in my case it is primary key) before merge and after this get correct CDC result?

Also, before merge runing folowing SP:

exec sp_MSforeachtable @command1='ALTER TABLE ? NOCHECK CONSTRAINT ALL'

And after merge folowing:

exec sp_MSforeachtable @command1='ALTER TABLE ? CHECK CONSTRAINT ALL'

You cannot update PK. All PK updates internally are handled as delete + insert — Alex, Jul 25 '17 at 22:15
CDC requires a PK on the source table and, as such, you cannot remove that PK as it is schema-locked. You should also note that an UPDATE operation is actually the result of a DELETE AND INSERT (ops 3 & 4 for CDC). You can see this by checking the __$operation column of the cdc.X table (this is the same behaviour found in After Update Triggers when you select from inserted & deleted). — LogicalMan, Aug 28 '17 at 18:52

How implement correctly MERGE for tables? Incorrect CDC result

0 Answers0