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

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

Create Tables.

  • dbo.ChangeTrackingYes – Change Tracking enabled
  • ChangeTrackingColumnsYes – Change Tracking enabled with TRACK_COLUMNS_UPDATED = ON
  • ChangeTrackingNo – Change Tracking is not enabled

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

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:

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:

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.

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

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

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

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.

We execute 3 transactions here.

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

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

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:

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:

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

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:

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.

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

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:

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

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

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

Delete database

Keep it simple :-)