FIX: Change tracking record is inconsistent during an update on a table that has a clustered or unique index in SQL Server

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core More

Symptoms


When you run an update on a table that has a clustered or unique index and the update occurs on a non-primary unique column, the change tracking record is inconsistent with the update statement.

For example, assume that a column that is named "column1" is included in the clustered or unique index. When the column value is changed from a larger to a smaller value (such as changing the value from 20 to 16), the change tracking side table receives the insert operation before the delete operation. For example, the transaction log insert row record operation is received before the delete row operation, or "I" is inserted before "D."

Sys_change_version

sys_change_create_version

sys_change_operation

sys_change_column

sys_change_context

PK_column

1116

19

I

NULL

NULL

5639485628

1116

20

D

NULL

NULL

5639485628


When the column value is changed from a smaller to a largeer value (such as changing the value from 16 to 20), "D" is inserted before "I."

Sys_change_version

sys_change_create_version

sys_change_operation

sys_change_column

sys_change_context

PK_column

1126

32

D

NULL

NULL

5639485628

1126

33

I

NULL

NULL

5639485628

Cause


This issue occurs because of incorrect ordering between delete/insert pairs in the change tracking side table.

Resolution


The fix for this problem is included in the following updates for SQL Server:

         Cumulative Update 8 for SQL Server 2017

         Cumulative Update 9 for SQL Server 2016 Service Pack 1

Cumulative Update 12 for SQL Server 2014 SP2

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More information


For a change tracking enabled table, when an update occurs on a non-primary column that is defined to be unique, two entries are inserted into the change tracking side table: One entry for each of the split actions, Insert and Delete.

When the CHANGETABLE function is called to enumerate the changes, these entries are sorted by primary key values, and then the operations are aggregated. If the update inserts a lower value, an “I” operation is run on the side table first, followed by a "D" operation. This causes an incorrect operation being returned for this row. This is because "I" followed "D" is aggregated as "D."

Because only the primary key values are maintained in the side tables, it is not necessary to have two separate entries. This is true as long as the primary key column is not updated.

This issue is fixed for non-primary unique columns. In this case, when the update occurs, only one row is inserted by having "U" as an update operation instead of two entries that have "D" and "I."

References


Learn about the terminology Microsoft uses to describe software updates.