CODEWITHSUNDEEP
sql-server-2008-r2change-data-capture
P. Kouvarakis
P. Kouvarakis

Reputation: 1943

CDC multiple insert/delete of the same identity value

I have a table T that contains an ID set as identity and primary key. I have enabled CDC on the table and then later added an XML field that I didn't care capturing so I did not do anything further (to recreate the capture table and/or migrate old capture data).

I now have a stored procedure that (among other things) updates only the newly created field (no other field) in table T. I notice that instead of recording an update (operation=3 followed by operation=4), CDC records a delete (operation=1) followed by an insert (operation=2) and all fields are the same (of course since none of them was updated)

I actually noticed this because I had the same identity value inserted and/or deleted more than once, which is not possible (unless identity_insert is on, which is not)

Why does CDC record operation=1 instead of 3 and operation=2 instead of 4? Is this documented anywhere or is it a bug?

Upvotes: 2

Views: 2695

Answers (1)

5x1llz
5x1llz

Reputation: 31

The reason you are seeing a Delete/insert pair (Operation number 1/2) as opposed to an update pair (3/4) is because you are updating a "set" of data that ALSO has a unique constraint on your column.

For SQL to make sense of this wihout violating the unique cosntraint, it deletes the row and reinserts it (with the "update").

More information on this. Its not an issue or a defect. its the way SQL works and CDC innocently logs it as it sees it. Remember, CDC is just a subscriber and replicates things as they happen.

If you have a need to see an update you may have to look for the 1/2 "pair" and not ONLY the operation code 3/4.

Some great articles: Bounded Update is the term used to describe certain types of UPDATE statements from the publisher that will replicate as DELETE/INSERT pairs on the subscriber. We perform a bounded update for every set based update that changes a column that is part of a unique index or constraint. In other words, if an UPDATE statement touches more than one row and modifies a column that is has any UNIQUE constraints, the UPDATE statement is sent to the subscriber as a DELETE/INSERT pair ... read more here

https://support.microsoft.com/en-us/kb/238254

Upvotes: 3

Related Questions