Change Tracking
Change Tracking is a lightweight feature that tracks changes (inserts, updates, deletes) to a table without capturing the actual data changes.
use the function
CHANGETABLE(CHANGES table_name, last_sync_version)
to retrieve the changes recorded by Change Tracking
use the sql to combine changes with the table to show rows that have been changed.
SELECT CT.*, C.*
FROM CHANGETABLE(CHANGES Customers, @last_sync_version) AS CT
LEFT JOIN Customers AS C
ON CT.CustomerID = C.CustomerID
To enable Change Tracking
-- Enable Change Tracking at the database level
ALTER DATABASE YourDatabase
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
-- Enable Change Tracking on a table
ALTER TABLE Customers
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
It requires a PRIMARY KEY available with table, and records minimum information about the changes.
Primary Key columns The primary key(s) of the changed rows.
SYS_CHANGE_VERSION The version when the change occurred.
SYS_CHANGE_OPERATION Type of change: 'I' (Insert), 'U' (Update), 'D' (Delete).
SYS_CHANGE_COLUMNS (Optional) Bitmask of changed columns (if column tracking is enabled).
SYS_CHANGE_CONTEXT (Optional) Context info if set during the change.
Compared to CDC (Change Data Capture) which keeps full row data (before and after for updates), Change Tracking is more lightweight by just keeping what type of changes have happen.
Also CDC doesn't require a primary key, it's based on a different mechanism.