SQL Server Change Tracking: When to Use It, How to Enable It and How It Differs from CDC

When a mobile app, a cache, a search index or a data warehouse needs to follow changes in SQL Server, the first ideas are usually triggers, UpdatedAt columns and hand-built log tables. SQL Server has a built-in, lightweight feature for this: Change Tracking. In the right scenario, it answers “which rows changed since the last sync?” with far less code and more reliably than triggers.

This guide explains what Change Tracking records and what it doesn’t, how it differs from Change Data Capture, how to enable it, how to write a reliable sync query, and what to watch in production.

What Change Tracking Records

Change Tracking records which row in a table changed and the type of change (insert, update, delete). It identifies the changed row by its primary key value; to get the row’s current state, the application joins back to the source table on that key. Optionally, it can also record which columns changed in an update.

Change Tracking does not record:

• The row’s old values or any intermediate values.
• How many times a row changed between two syncs; only that it changed.
• Who made the change.

Even an UPDATE that sets a column to the same value is recorded as a change.

Change Tracking, CDC or a Temporal Table?

In short: if “what changed since the last sync, give me the latest state” is enough, use Change Tracking; if you need the values of every change, use CDC; if you need a row as it was at a given moment, use a temporal table.

How to Enable Change Tracking

Enable it at database level first, then for each table to be tracked:

ALTER DATABASE SalesDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE dbo.Customer
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

-- Snapshot isolation for consistent syncs
ALTER DATABASE SalesDB SET ALLOW_SNAPSHOT_ISOLATION ON;

CHANGE_RETENTION sets how long change information is kept. It should be longer than your longest sync interval, including the longest time clients can stay offline. TRACK_COLUMNS_UPDATED also records which columns changed, but uses extra space; enable it only if you really need it.

A Reliable Sync Query

The syncing application stores the version number to use next time on every run. The right pattern does three things together: runs inside snapshot isolation, checks that the last version is still valid, and joins the changes to the source table:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;

DECLARE @last_sync bigint = 1500;   -- last version stored by the application
DECLARE @current   bigint = CHANGE_TRACKING_CURRENT_VERSION();

IF @last_sync < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.Customer'))
BEGIN
    -- Change information has been cleaned up: a full reload is needed
    SELECT N'FULL_RELOAD' AS action;
END
ELSE
BEGIN
    SELECT ct.CustomerId, ct.SYS_CHANGE_OPERATION, c.Name, c.City
    FROM CHANGETABLE(CHANGES dbo.Customer, @last_sync) AS ct
    LEFT JOIN dbo.Customer AS c ON c.CustomerId = ct.CustomerId;   -- deleted rows return NULL
END

SELECT @current AS next_sync_version;   -- the application stores this
COMMIT;

Snapshot isolation prevents new transactions arriving between the moment you read the version and the moment you read the changes from mixing up the result.

What to Watch in Production

• Synchronous overhead: Change Tracking information is written in the same transaction as the DML itself. Test the overhead on tables with very heavy writes.
• Internal tables can grow: each tracked table has an internal (side) table, and the database has a commit table. If automatic cleanup falls behind, these tables grow and queries slow down.
• Manual cleanup: for cases where automatic cleanup can’t keep up, there’s the sys.sp_flush_CT_internal_table_on_demand procedure.
• Short retention, long outages: short retention saves space but forces clients that stay offline for long into a full reload. Strike a deliberate balance between the two.

To monitor the size of the internal tables:

SELECT OBJECT_NAME(it.parent_object_id) AS tracked_table,
       it.name AS internal_table,
       SUM(ps.row_count) AS row_count,
       SUM(ps.reserved_page_count) * 8 / 1024.0 AS reserved_mb
FROM sys.internal_tables AS it
JOIN sys.dm_db_partition_stats AS ps ON ps.object_id = it.object_id
WHERE it.internal_type_desc = N'CHANGE_TRACKING'
GROUP BY it.parent_object_id, it.name
ORDER BY reserved_mb DESC;

Where Is It Enabled? How Do You Turn It Off?

-- Databases and tables with Change Tracking enabled
SELECT DB_NAME(database_id) AS database_name, is_auto_cleanup_on,
       retention_period, retention_period_units_desc
FROM sys.change_tracking_databases;

SELECT OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id) AS table_name,
       is_track_columns_updated_on
FROM sys.change_tracking_tables;

-- To turn it off: tables first, then the database
ALTER TABLE dbo.Customer DISABLE CHANGE_TRACKING;
ALTER DATABASE SalesDB SET CHANGE_TRACKING = OFF;

Checklist

• Change Tracking is chosen when the need is “which row changed”, CDC when it’s “which values changed”.
• Tracked tables have a primary key.
• Retention is longer than the longest sync interval.
• Syncs run in snapshot isolation and check MIN_VALID_VERSION.
• Internal table size and automatic cleanup are monitored.