Sample Header Ad - 728x90

Change Tracking sometimes returns new inserts without primary key values

1 vote
0 answers
75 views
We utilize Change Tracking feature to catch inserts and updates for couple of tables. Queries are like this: SELECT P.* ,CT.SYS_CHANGE_OPERATION ,TC.commit_time FROM schema.tablename AS P RIGHT OUTER JOIN CHANGETABLE(CHANGES schema.tablename, 438750) AS CT ON CT.ID = P.ID LEFT OUTER JOIN sys.dm_tran_commit_table TC on CT.sys_change_version = TC.commit_ts WHERE SYS_CHANGE_OPERATION IN ('I','U') /* INSERTS and UPDATES */ AND SYS_CHANGE_VERSION <= '439766' /* = CHANGE_TRACKING_CURRENT_VERSION */ From time to time results may contain new inserts which doesn't have primary keys. Below there's an example of one case. First row is a header and in this case the first column (ID) is primary key. First four rows are ok but after that we have these odd cases which are listed being as type SYS_CHANGE_OPERATION="I" (=insert) but all values are missing, including primary key: ID|COLUMN_1|COLUMN_2|COLUMN_3|COLUMN_4|COLUMN_5|COLUMN_6|COLUMN_7|COLUMN_8|COLUMN_9|COLUMN_10|COLUMN_11|COLUMN_12|COLUMN_13|COLUMN_14|COLUMN_15|COLUMN_16|COLUMN_17|COLUMN_18|COLUMN_19|COLUMN_20|COLUMN_21|COLUMN_22|COLUMN_23|COLUMN_24|COLUMN_25|COLUMN_26|SYS_CHANGE_OPERATION|commit_time
130956348|9|1540|0.614|160|10631423|57|65|62|70|9||||||1|||0.099||||||168|1.412| Does anyone have ran into similar issues using Change Tracking? What could be the reason for this kind of behaviour?
Asked by Markku (11 rep)
Jan 31, 2024, 01:27 PM