I have setup up SQL Server Change Tracking on a table t1 on MS SQL2008R2. Within a transaction, I insert a row into it and update it 'n' times. When I examine the rowcounts in the change tracking tables, it shows 'n' number of rows whereas the syscommitab shows just 1 row. Since the change tracking mechanism is a synchronous mechanism and per documentation, is expected to track only "net change" which occurred in a transaction, why is the change tracking table showing 'n' rows instead of just 1 ? Here is the SQL that was used to examine the rowcount.

SELECT  sct1.name as CT_schema,
    sot1.name as CT_table,
    ps1.row_count as CT_rows,
    ps1.reserved_page_count*8./1024. as CT_reserved_MB,
    sct2.name as tracked_schema,
    sot2.name as tracked_name,
    ps2.row_count as tracked_rows,
    ps2.reserved_page_count*8./1024. as tracked_base_table_MB,
    change_tracking_min_valid_version(sot2.object_id) as min_valid_version
FROM sys.internal_tables it
JOIN sys.objects sot1 on it.object_id=sot1.object_id
JOIN sys.schemas AS sct1 on
JOIN sys.dm_db_partition_stats ps1 on
it.object_id = ps1. object_id
and ps1.index_id in (0,1)
LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id
LEFT JOIN sys.schemas AS sct2 on
LEFT JOIN sys.dm_db_partition_stats ps2 on
sot2.object_id = ps2. object_id
and ps2.index_id in (0,1)
WHERE it.internal_type IN (209, 210)
and (sot2.name='t1' or sot1.name='syscommittab')

Above SQL courtesy of https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/


Change Tracking keeps watch over every single DML operation; it has no concept of a transaction. So if you change a row 5 times, whether in a transaction or not, you're going to get 5 rows. From Microsoft's documentation:

In change tracking, the tracking mechanism involves synchronous tracking of changes in line with DML operations so that change information is available immediately.

It doesn't contain any more complicated logic than that (like only recording the net change). You can of course determine net changes by using the Change Tracking functions instead of querying the CT table directly. You can manually collapse duplicate rows in the change tracking table by grouping in your query, or maybe Change Data Capture might be more what you're after?

If you want something that is transaction-aware and only records net changes, you can look at Temporal Tables. You're going to have to move to a version of SQL Server produced this decade, though (2016 or 2017).

  • I was under the assumption that since the Change Tracking functions give you a view of the "net changes", that is how the underlying data itself is also stored. Thank you for correcting the misconception. The application that I am working has insert triggers and these have some cute logic within it to update the inserted rows. And these updates get reflected in the CT table. And hence the question. I am restricted to MS SQL2008R2 Standard Edition. So I cannot use the CDC or Temporal tables. – QFirstLast Nov 30 '17 at 16:27
  • I tried to mark you answer as accepted but my "reputation" is not high enough for the vote to count – QFirstLast Nov 30 '17 at 16:28
  • @QFirstLast There is no rush to accept an answer (a better answer might come along), but you shouldn't be limited by reputation to hit the check mark. – Aaron Bertrand Nov 30 '17 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.