I am testing Change Tracking on some of the exiting tables. Have implemented CT on an existing table with 100 rows. What I observed is, when I delete & reinsert the same rows, the "SYS_CHANGE_OPERATION" column shows as Update 'U', the reason that I understand is the that the CT temp tables holds the data of the PK column, compares & then shows if the data was updated or Inserted. To me, if the data was deleted & reinserted with the same PK & row details, it should be shown as Insert i/o Update.
On the other hand, if a new row was inserted after CT was enabled on the table & if it was deleted & reinserted, it will show correctly as D Delete & 'I' insert after the same row was inserted which is correct, however if we fire a update statement on the same row that was inserted after CT was enabled, the "SYS_CHANGE_OPERATION" column still shows as 'I' Insert i/o Update 'U' with a new version.
I am totally confused with CT's design, can someone help me explain please ??