T-SQL: Change Tracking – What is this and how it works


Change tracking was introduced in SQL Server 2008. This is a tool that keeps the fact that there are changes in tracked tables for a specified period of time.

It doesn’t show what the changes are, it tells that a data has been changed.

It answers if a change has been made only for the tables, for which Change Tracking is enabled.

It keeps this information (if there is a change and in which table) for a defined period of time, called Retention Period. I’ll play with this in the next post.

Let’s create test database and INSERT/UPDATE/DELETE some data. Next (in a future post) we’ll give you an example how to use Change Tracking in ETL SSIS project.

Create the database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

First we need to enable Change Tracking on database level. Here we define what is the retention period.

ALTER DATABASE [PL]
	SET CHANGE_TRACKING = ON
	(CHANGE_RETENTION = 3 DAYS);
GO

Create Tables.

  • dbo.ChangeTrackingYes – Change Tracking enabled
  • ChangeTrackingColumnsYes – Change Tracking enabled with TRACK_COLUMNS_UPDATED = ON
  • ChangeTrackingNo – Change Tracking is not enabled
-- dbo.ChangeTrackingYes does not track columns updates
CREATE TABLE dbo.ChangeTrackingYes
	(
		ChangeTrackingYesID INT IDENTITY(1, 1) PRIMARY KEY
		, [Value] NVARCHAR(128)
	);
GO

ALTER TABLE dbo.ChangeTrackingYes
	ENABLE CHANGE_TRACKING;
GO

-- dbo.ChangeTrackingColumnsYes tracks columns updates
CREATE TABLE dbo.ChangeTrackingColumnsYes
	(
		ChangeTrackingColumnsYesID INT IDENTITY(1, 1) PRIMARY KEY
		, Value1 NVARCHAR(128)
		, [Value2] NVARCHAR(128)
	);
GO

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

-- No Change Tracking
CREATE TABLE dbo.ChangeTrackingNo
	(
		ChangeTrackingNoID INT IDENTITY(1, 1) PRIMARY KEY
		, [Value] NVARCHAR(128)
	);
GO

Information for the tracked databases and tables can be found in the Object Catalog:

SELECT
	DB_NAME([database_id]) AS DatabsaseName
	, *
FROM sys.change_tracking_databases;
GO

SELECT
	OBJECT_SCHEMA_NAME([object_id]) AS SchemaName
	, OBJECT_NAME([object_id]) AS ObjectName
	, '<--->' AS ' '
	, *
FROM sys.change_tracking_tables;
GO

SELECT
	OBJECT_SCHEMA_NAME([object_id]) AS SchemaName
	, '<--->' AS ' '
	, *
FROM sys.internal_tables
WHERE
	parent_object_id IN
	(
		SELECT [object_id]
		FROM sys.change_tracking_tables
	);
GO

To query internal tables, login with DAC (Dedicated Administrator Connection):

  1. Click on “Database Engine Query” (next to “New Query” icon)
  2. Add “ADMIN:” as prefix to server name and use “sa” account (member of sysadmin)

DAC can’t be user for network connections, i.e. we need to be logged in on the same server where the SQL Server instance is installed.

In the new window that is using the DAC:

USE PL;
GO

SELECT * FROM sys.change_tracking_565577053;
GO

SELECT * FROM sys.change_tracking_613577224;
GO

Don’t close the DAC. We’ll query the internal tables in few minutes.

Every transaction against tracked table will increase the CT Version (Change Tracking Version) with 1. To check the current version:

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;
GO

0 is the initial value
NULL means that Change Tracking is not enabled

To see what the changes are, we use the built-in function CHANGETABLE with argument CHANGES. Let’s see what we have so far.

SELECT CT.* FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT;
GO

Query the changes with NULL (same as 0) as current CT Version:

SELECT CT.* FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, NULL) AS CT;
GO

And the other table – the one that keeps the column changes:

SELECT CT.* FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 0) AS CT;
GO

The last column is the primary key of the tracked table. The tables that we don’t track can’t be checked for changes:

SELECT CT.* FROM CHANGETABLE(CHANGES dbo.ChangeTrackingNo, 0) AS CT;
GO

What we’ll do now negates the above statement, but we’ll do it. Let’s play (INSERT/UPDATE/DELETE) with the not tracked table.

INSERT dbo.ChangeTrackingNo ([Value]) SELECT 'AI';
INSERT dbo.ChangeTrackingNo ([Value]) SELECT 'BI';
INSERT dbo.ChangeTrackingNo ([Value]) SELECT 'CI';
GO

We execute 3 transactions here.

SELECT * FROM dbo.ChangeTrackingNo;
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;

UPDATE dbo.ChangeTrackingNo SET [Value] = 'AU' WHERE [Value] = 'AI';
UPDATE dbo.ChangeTrackingNo SET [Value] = 'BU' WHERE [Value] = 'BI';
UPDATE dbo.ChangeTrackingNo SET [Value] = 'CU' WHERE [Value] = 'CI';
GO

SELECT * FROM dbo.ChangeTrackingNo;
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;

DELETE dbo.ChangeTrackingNo WHERE [Value] = 'AU';
DELETE dbo.ChangeTrackingNo WHERE [Value] = 'BU';
DELETE dbo.ChangeTrackingNo WHERE [Value] = 'CU';
GO

SELECT * FROM dbo.ChangeTrackingNo;
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;

This table is not tracked. Let’s move on and insert some data in the tracked tables:

INSERT dbo.ChangeTrackingYes ([Value]) SELECT 'AI';
INSERT dbo.ChangeTrackingYes ([Value]) SELECT 'BI';
INSERT dbo.ChangeTrackingYes ([Value]) SELECT 'CI';
GO

SELECT *
FROM dbo.ChangeTrackingYes;
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;
GO

We executed 3 transactions and the CT Version is 3. Let’s query CHANGETABLE with all the versions until 4 (future version);

SELECT CT.*, '<--->', 0 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT;
SELECT CT.*, '<--->', 1 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 1) AS CT;
SELECT CT.*, '<--->', 2 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 2) AS CT;
SELECT CT.*, '<--->', 3 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 3) AS CT;
SELECT CT.*, '<--->', 4 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 4) AS CT; -- Future Version
GO

Column SYS_CHANGE_OPERATION = ‘I’ keeps the type of the change. “I” means insert
Column ChangeTrackingYesID keeps the PK of the changes row.

As the current version is the last and there are no changes after, we query with version, less than the current version. This is how Change Tracking works: If the versions on the “source” and the “destination” servers are the same, nothing is selected. This is what we’ll do when I explain how use Change Tracking in SSIS ETL project.

Insert data in the table that tracks the columns:

INSERT dbo.ChangeTrackingColumnsYes (Value1, Value2) SELECT 'AI', 'XI';
INSERT dbo.ChangeTrackingColumnsYes (Value1, Value2) SELECT 'BI', 'YI';
INSERT dbo.ChangeTrackingColumnsYes (Value1, Value2) SELECT 'CI', 'ZI';
GO

SELECT *
FROM dbo.ChangeTrackingColumnsYes;
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;
GO

As expected – 3 transactions are executed and the version is 6. Change Tracking is changed on every transaction against tracked table.

The changes in the “columns” table:

SELECT CT.*, '<--->', 0 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 0) AS CT;
SELECT CT.*, '<--->', 1 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 1) AS CT;
SELECT CT.*, '<--->', 2 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 2) AS CT;
SELECT CT.*, '<--->', 3 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 3) AS CT;
SELECT CT.*, '<--->', 4 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 4) AS CT;
SELECT CT.*, '<--->', 5 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 5) AS CT;
SELECT CT.*, '<--->', 6 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 6) AS CT;
SELECT CT.*, '<--->', 7 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 7) AS CT; -- Future Version
GO

Let’s UPDATE the data in the first table, the one that doesn’t track the columns:

UPDATE dbo.ChangeTrackingYes SET [Value] = 'AU' WHERE [Value] = 'AI';
UPDATE dbo.ChangeTrackingYes SET [Value] = 'BU' WHERE [Value] = 'BI';
UPDATE dbo.ChangeTrackingYes SET [Value] = 'CU' WHERE [Value] = 'CI';
GO

SELECT * FROM dbo.ChangeTrackingYes;
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;
GO

SELECT CT.*, '<--->', 0 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT;
SELECT CT.*, '<--->', 1 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 1) AS CT;
SELECT CT.*, '<--->', 2 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 2) AS CT;
SELECT CT.*, '<--->', 3 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 3) AS CT;
SELECT CT.*, '<--->', 4 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 4) AS CT;
SELECT CT.*, '<--->', 5 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 5) AS CT;
SELECT CT.*, '<--->', 6 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 6) AS CT;
SELECT CT.*, '<--->', 7 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 7) AS CT;
SELECT CT.*, '<--->', 8 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 8) AS CT;
SELECT CT.*, '<--->', 9 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 9) AS CT;
SELECT CT.*, '<--->', 10 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 10) AS CT; -- Future Version
GO

In column SYS_CHANGE_CREATION_VERSION is the original version
In column SYS_CHANGE_VERSION is the updated version
In column SYS_CHANGE_OPERATION, “U” means Update

Delete some data:

DELETE dbo.ChangeTrackingYes WHERE [Value] = 'AU';
DELETE dbo.ChangeTrackingYes WHERE [Value] = 'BU';
DELETE dbo.ChangeTrackingYes WHERE [Value] = 'CU';
GO

SELECT * FROM dbo.ChangeTrackingYes;
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;
GO

SELECT CT.*, '<--->', 0 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT;
SELECT CT.*, '<--->', 1 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 1) AS CT;
SELECT CT.*, '<--->', 2 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 2) AS CT;
SELECT CT.*, '<--->', 3 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 3) AS CT;
SELECT CT.*, '<--->', 4 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 4) AS CT;
SELECT CT.*, '<--->', 5 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 5) AS CT;
SELECT CT.*, '<--->', 6 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 6) AS CT;
SELECT CT.*, '<--->', 7 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 7) AS CT;
SELECT CT.*, '<--->', 8 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 8) AS CT;
SELECT CT.*, '<--->', 9 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 9) AS CT;
SELECT CT.*, '<--->', 10 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 10) AS CT; 
SELECT CT.*, '<--->', 11 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 11) AS CT; 
SELECT CT.*, '<--->', 12 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 12) AS CT; 
SELECT CT.*, '<--->', 13 AS [Version] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 13) AS CT; -- Future Version
GO

Column SYS_CHANGE_OPERATION: “I” is Insert; “U” is Update; “D” is Delete. We will filter on this column to select the change that we need.

Let’s UPDATE the table that tracks columns.

UPDATE dbo.ChangeTrackingColumnsYes SET Value1 = 'AU' WHERE Value1 = 'AI';
UPDATE dbo.ChangeTrackingColumnsYes SET Value2 = 'YU' WHERE Value2 = 'YI';
UPDATE dbo.ChangeTrackingColumnsYes SET Value1 = 'CU' WHERE Value1 = 'CI';
GO

SELECT * FROM dbo.ChangeTrackingColumnsYes;
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;
GO

The query that we use to show the changes needs to be transformed to a dynamic one:

DECLARE
	@Counter TINYINT = 1
	, @DynamicSQL NVARCHAR(MAX);

WHILE (@Counter <= 16)
BEGIN
	SET @DynamicSQL = N'
	SELECT
		CT.*
		, ''<--->'' AS '' ''
		, CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(''PL.dbo.ChangeTrackingColumnsYes''), ''Value1'', ''ColumnID''), CT.SYS_CHANGE_COLUMNS) AS IsValue1_Changed
		, CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(''PL.dbo.ChangeTrackingColumnsYes''), ''Value2'', ''ColumnID''), CT.SYS_CHANGE_COLUMNS) AS IsValue2_Changed -- CHANGE_TRACKING_IS_COLUMN_IN_MASK(ColumnID, Change Columns)
		, ' + CAST(@Counter AS NVARCHAR(2)) + N' AS [Version]
	FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, ' + CAST(@Counter AS NVARCHAR(2)) + N') AS CT
	';

	EXEC (@DynamicSQL);

	SET @Counter += 1;
END

We used another built-in function CHANGE_TRACKING_IS_COLUMN_IN_MASK to determine if there is a change in the columns that we track.

Delete from the table that tracks columns:

DELETE dbo.ChangeTrackingColumnsYes WHERE Value1 = 'AU';
DELETE dbo.ChangeTrackingColumnsYes WHERE Value2 = 'YU';
DELETE dbo.ChangeTrackingColumnsYes WHERE Value1 = 'CU';
GO

SELECT * FROM dbo.ChangeTrackingYes;
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;
GO

DECLARE
	@Counter TINYINT = 1
	, @DynamicSQL NVARCHAR(MAX);

WHILE (@Counter <= 19)
BEGIN
	SET @DynamicSQL = N'
	SELECT
		CT.*
		, ''<--->'' AS '' ''
		, CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(''PL.dbo.ChangeTrackingColumnsYes''), ''Value1'', ''ColumnID''), CT.SYS_CHANGE_COLUMNS) AS IsValue1_Changed
		, CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(''PL.dbo.ChangeTrackingColumnsYes''), ''Value2'', ''ColumnID''), CT.SYS_CHANGE_COLUMNS) AS IsValue2_Changed -- CHANGE_TRACKING_IS_COLUMN_IN_MASK(ColumnID, Change Columns)
		, ' + CAST(@Counter AS NVARCHAR(2)) + N' AS [Version]
	FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, ' + CAST(@Counter AS NVARCHAR(2)) + N') AS CT
	';

	EXEC (@DynamicSQL);

	SET @Counter += 1;
END

Go back to the window that is using DAC and execute:

USE PL;
GO

SELECT * FROM sys.change_tracking_565577053;
GO

SELECT * FROM sys.change_tracking_613577224;
GO

To check the version for specified row, we use the built-in function CHANGETABLE with argument VERSION:

SELECT * FROM CHANGETABLE(VERSION dbo.ChangeTrackingColumnsYes, (ChangeTrackingColumnsYesID), (1)) AS CT
SELECT * FROM CHANGETABLE(VERSION dbo.ChangeTrackingColumnsYes, (ChangeTrackingColumnsYesID), (2)) AS CT
SELECT * FROM CHANGETABLE(VERSION dbo.ChangeTrackingColumnsYes, (ChangeTrackingColumnsYesID), (3)) AS CT
SELECT * FROM CHANGETABLE(VERSION dbo.ChangeTrackingColumnsYes, (ChangeTrackingColumnsYesID), (4)) AS CT

The last argument is the ID (the Primary Key) that we need to verify.

Delete database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

Keep it simple :-)