Why does the MS SQL Server change tracking table store all DML operations that occurred within the transactiton? 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
sot1.schema_id=sct1.schema_id
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
sot2.schema_id=sct2.schema_id
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).