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 larger 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
About SQL Server builds
Each new build for SQL Server contains all the hotfixes and security fixes that were in the previous build. We recommend that you install the latest build for SQL Server:
Latest cumulative update for SQL Server 2017
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.
