Change Tracking (CT) introduced in SQL2008, all edtitions, includes Express.
CT is more like a “motion sensor”. CT tells you which rows changed and what the latest values are, but id doesn’t keep a history of every intermediate step. CDC is like a video recording of every single change. CT is much more lightweight than CDC.
CT can only be enabled on table with PK. It’s a sync operation (overhead on every transaction).
CT doesn’t support updating of PK value. When change a PK, it’s tracked as a Delete of the old Pk record and an Insert of the new PK record.
We must enable CT at database level first, then on each table.
Enable at database level
ALTER DATABASE YourDatabaseName
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
Enable at table level
ALTER TABLE dbo.tblLIBAltCode
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON); -- Set to ON so we can see WHICH columns changed
To query changes
To get changes, we need to know the Version Number we last looked at. When we start for the first time, we get the “Current Version”.
-
- Get the Current Version (the “Baseline”)
DECLARE @last_synchronization_version bigint;
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();
-- Store this value in a table somewhere for the next time you run your sync
-
- Query for changes since the last version
DECLARE @last_sync_version bigint = 0; -- In a real app, load this from a 'SyncSettings' table
SELECT
CT.ID, -- The Primary Key (always available in CT)
CT.SYS_CHANGE_OPERATION, -- I, U, or D
CT.SYS_CHANGE_VERSION, -- Version for tracking
T.AltCode, -- Data (will be NULL if Operation is 'D')
T.Description, -- Data (will be NULL if Operation is 'D')
CASE
WHEN CT.SYS_CHANGE_OPERATION = 'D' THEN 1
ELSE 0
END AS IsDeleted
FROM CHANGETABLE(CHANGES dbo.tblLIBAltCode, @last_sync_version) AS CT
LEFT JOIN dbo.tblLIBAltCode AS T -- use LEFT JOIN to handle Delete
ON CT.ID = T.ID -- Always join on the Primary Key
ORDER BY CT.SYS_CHANGE_VERSION;
-
- Checking speicfic columns
DECLARE @last_version bigint = 0;
DECLARE @ColumnID int = COLUMNPROPERTY(OBJECT_ID('dbo.tblLIBAltCode'), 'AltCode', 'ColumnId');
SELECT
CT.SYS_CHANGE_OPERATION,
CT.ID,
[AltCodeChanged] = CHANGE_TRACKING_IS_COLUMN_IN_MASK(@ColumnID, CT.SYS_CHANGE_COLUMNS)
FROM CHANGETABLE(CHANGES dbo.tblLIBAltCode, @last_version) AS CT;
Changing Tracking has retention period as well, default is 2 days. If @last_sync_version is older than the database retension period, the query will retun error. Use CHANGE_TRACKING_MIN_VALID_VERSION() to validate @last_sync_version.
To check current retention setting
SELECT
DB_NAME(database_id) AS DatabaseName,
retention_period,
retention_period_units_desc
FROM sys.change_tracking_databases;
To change retention setting
-- Example: Change retention to 7 days
ALTER DATABASE YourDatabaseName
SET CHANGE_TRACKING (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);