2

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?

P. Kouvarakis
  • 1,893
  • 12
  • 21

1 Answers1

3

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

5x1llz
  • 31
  • 3
  • Very interesting but it does not actually answer my question. In my case I only update the XML field which is not part of any unique constraint. – P. Kouvarakis Oct 02 '16 at 12:38