Sql Server: Change Tracking

What is it ?

Change Tracking is a synchronous mechanism which modifies change tracking tables as part of ongoing transactions to indicate when a row has been changed. It does not record past or intermediate versions of the data itself, only that a change has occurred. It is recommended to use snapshot isolation with Change Tracking!

Change Tracking was introduced with SQL Server 2008+ (all editions). It has a lot of similarities, but also differences with Change Data Capture (CDC), which is also a feature of SQL Server 2008+ (but only DataCenter en Enterprise editions).

For a good explanation, see also:

Using Change Tracking in SQL Server 2008

https://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/

Prerequisites

Change Tracking is available since SQL Server 2008 and on Azure SQL.

Tracked tables need to have a primary key.

To use change tracking, the database compatibility level must be set to 90 or greater. If a database has a compatibility level of less than 90, you can configure change tracking. However, the CHANGETABLE function, which is used to obtain change tracking information, will return an error.

Using snapshot isolation is the easiest way for you to help ensure that all change tracking information is consistent. For this reason, we strongly recommend that snapshot isolation be set to ON for the database. For more information, see Workwith Change Tracking (SQL Server).

Enabling change tracking

To setup change tracking on a database:

ALTER DATABASE Samples2012

SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

To change afterwards:

ALTER DATABASE Samples2012

SET CHANGE_TRACKING (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

To enable change tracking on a table after it has been setup on the database:

ALTER TABLE invoicing.Customer

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = OFF)

By default, TRACK_COLUMNS_UPDATED is OFF. If you set it on, SQL Server will also keep track of which columns were updated with an update statement.

Verifying change tracking

Querying the sys.change_tracking_databases catalog view lists the databases for which change tracking is enabled:

SELECT

       td.database_id,

       d.name AS database_name,

       td.is_auto_cleanup_on,

       td.max_cleanup_version,

       td.retention_period,

       td.retention_period_units,

       td.retention_period_units_desc

FROM sys.change_tracking_databases td

INNER JOIN sys.databases d ON td.database_id = d.database_id

Querying the sys.change_tracking_tables catalog view lists the tables of the current database for which change tracking is enabled:

SELECT

    tt.object_id,

    s.name AS schema_name,

    t.name AS table_name,

    tt.is_track_columns_updated_on,

    tt.min_valid_version,

    tt.begin_version,

    tt.cleanup_version

FROM sys.change_tracking_tables tt

INNER JOIN sys.tables t ON (tt.object_id = t.object_id)

INNER JOIN sys.schemas s ON (t.schema_id = s.schema_id)

ORDER BY 2, 3

Using change tracking

All changes are tracked with a change version. The first ever created change tracking record (over all tables) has version 1.

When AUTO_CLEANUP is started, older versions get removed out of the change tracking tables, and the oldest (minimum) version tracked

With CHANGETABLE(CHANGES Schema.Table, @VersionAlreadyRead) the list of changes can be queried. I.e:

SELECT *

FROM CHANGETABLE(CHANGES invoicing.Customer, 6) AS CustomerChanges

ORDER BY 1

Returns all changes on table Customer with version > 6:

Note the last column(s) containing the primary key which you can use to join with the table to get actual values.

SYS_CHANGE_VERSION contains the version number. Two rows have version 8 which means they both resulted from the execution of a single SQL statement.

SYS_CHANGE_OPERATION contains I for INSERT, U for UPDATE or D for DELETE.

For instance, to get the actual values of the recently inserted records:

SELECT c.*, cc.SYS_CHANGE_OPERATION, cc.SYS_CHANGE_VERSION, cc.Id AS CHANGEDROW_ID

FROM CHANGETABLE(CHANGES invoicing.Customer, 7) AS cc

LEFT OUTER JOIN invoicing.Customer AS c ON (cc.Id = c.Id)

ORDER BY cc.SYS_CHANGE_VERSION

To get the last used CHANGE_VERSION in the database:

SELECT CHANGE_TRACKING_CURRENT_VERSION()

To get the oldest/smallest/minimum version number still available (not yet AutoCleaned) for a given table:

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('invoicing.Customer'))

Disabling change tracking

To disable change tracking on a table:

ALTER TABLE invoicing.Customer

DISABLE CHANGE_TRACKING

On the database:

ALTER DATABASE Samples2012

SET CHANGE_TRACKING = OFF

Note: Change tracking must first be disabled for all change-tracked tables before change tracking can be set to OFF for the database. To determine the tables that have change tracking enabled for a database, use the sys.change_tracking_tables catalog view.

More information

·         About Change Tracking (SQL Server)
https://msdn.microsoft.com/en-us/library/bb933875(v=sql.130).aspx

·         Enable and Disable Change Tracking (SQL Server)
https://msdn.microsoft.com/en-us/library/bb964713(v=sql.130).aspx

·         Work with Change Tracking (SQL Server)
https://msdn.microsoft.com/en-us/library/bb933874(v=sql.130).aspx

·         Change Data Capture and Other SQL Server Features
About change tracking, database mirroring, transactional replication, restoring and attacching a database enabled for Change Data Capture.
https://msdn.microsoft.com/en-us/library/cc645938.aspx

·         Performance Tuning SQL Server Change Tracking
https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/

·         Change Tracking Cleanup Limitation
http://sirsql.net/content/2014/04/03/201443change-tracking-cleanup-limitation/