Introduction to Sql server change tracking

SQL Server change tracking (CT) is a mechanism whereby you can track the kind of changes happening on one or more database tables in a versioned manner. You get to know what kind of changes happened e.g. Insert, update or delete and also you can enable change tracking on columns to get to know what columns changed. This is different from CDC or Change data capture (take a look at differences).

Remember, CT does not track the whole row which changed but only the primary keys of the rows which changed and additionally the column values which changed (if it’s enabled).

Let’s see how this works.

Initialize database

let’s create a database and some tables with data on which we will later enable change tracking.

Create database:

USE master
GO

IF EXISTS(select * from sys.databases where name='CTDemoDb')
BEGIN
DROP DATABASE CTDemoDb
END

CREATE DATABASE CTDemoDb
GO

Create table

USE CTDemoDb
GO
CREATE TABLE [dbo].[Person]
(
Id INT PRIMARY KEY,
FirstName VARCHAR(30),
LastName VARCHAR(100),
DateOfBirth DATETIME,
ModifiedDate DATETIME
)

Fill Records

USE [CTDemoDb]
INSERT INTO [dbo].[Person]
([Id]
,[FirstName]
,[LastName]
,[DateOfBirth]
,[ModifiedDate])
VALUES
(1
,'Jack'
,'Ryan'
,'1982-7-7'
,getdate())

INSERT INTO [dbo].[Person]
([Id]
,[FirstName]
,[LastName]
,[DateOfBirth]
,[ModifiedDate])
VALUES
(2
,'George'
,'Clooney'
,'1977-7-12'
,getdate())

INSERT INTO [dbo].[Person]
([Id]
,[FirstName]
,[LastName]
,[DateOfBirth]
,[ModifiedDate])
VALUES
(3
,'Brad'
,'Pitt'
,'1964-10-10'
,getdate())

GO

Enable change tracking

Now let’s enable change tracking. Remember that this has to be done separately on the database and each individual table.

Enable change tracking on the database

USE master
GO
ALTER DATABASE CTDemoDb
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS,AUTO_CLEANUP = ON)
GO

Enable change tracking on the table.

USE CTDemoDb
GO
ALTER TABLE dbo.Person ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

Change tracking versioning

Results or data related to change tracking is accessed using functions provided by SQL server. Before that lets briefly discuss the versioning in change tracking.

• CT version is an integer and increases sequentially
• Each change (or to be more precise each transaction) increases the version number by one.
• The CT version is not specific to a table but database and increases whenever there is any change made on a tracked table

The common way of working with CT is where each client fetches the required CT information and keeps the version it read last time with itself. Next time it will use this version and get all the changes relative to his / her last synced version i.e. all the changes between the last synced version and Change tracking current version and store the new synced version with itself.

Apart from this, there is one more important number called change tracking minimum valid version. This tells if your last synced version (for a table) is valid at any given point of time for fetching the changes. There are cases where your last synced version might be invalid like

• Table getting truncated
• CT clean up ran which cleared all the tracked changes
• CT was not enabled on a table

CHANGE_TRACKING_CURRENT_VERSION():  Gives the current version of change tracking

CHANGE_TRACKING_MIN_VALID_VERSION() : Gives the minimum valid version

CHANGETABLE(): Gets all the changes for a table.

Change tracking queries

USE CTDemoDb
GO
DECLARE @PREVIOUS_SYNCED_VERSION BIGINT
DECLARE @CURRENT_CHANGE_VERSION BIGINT

SET @PREVIOUS_SYNCED_VERSION = 0 -- Here you need to use the version till which you already synced last time
SET @CURRENT_CHANGE_VERSION = CHANGE_TRACKING_CURRENT_VERSION()
SELECT @CURRENT_CHANGE_VERSION CURRENT_CHANGE_VERSION
SELECT CHANGE_TRACKING_MIN_VALID_VERSION ( OBJECT_ID('dbo.Person'))
SELECT * FROM CHANGETABLE(CHANGES dbo.Person, @PREVIOUS_SYNCED_VERSION) AS CT1 ORDER BY SYS_CHANGE_VERSION

Let’s see this in action.

Insert

USE CTDemoDb
GO
INSERT INTO [dbo].[Person]
([Id]
,[FirstName]
,[LastName]
,[DateOfBirth]
,[ModifiedDate])
VALUES
(4
,'Clint'
,' Eastwood'
,'1964-11-10'
,getdate())

Results from CT

Here are the results using the above change tracking queries.

Refer Microsoft documentation to know the meaning of all the output columns.

Update

USE CTDemoDb
GO
UPDATE [dbo].[Person] SET [DateOfBirth] = '1963-11-9' WHERE ID = 4

Results from CT

Now at this point, you must be wondering why the change operation says ‘I’ i.e. insert and this is one of the most important concepts to understand here. The ‘SYS_CHANGE_OPERATION’ is always relative to the last sync version passed to CHANGETABLE function. In this case, we passed 0 and relative to that version it is still an insert.

Another way to look and understand this scenario is how a client getting the CT information from this database would like to use these changes. For example, let’s say I last synced my changes at version 0. Now after that there was an insert and further, there was an update on the same record so the current version is 2.Now when I again sync changes for me I should get the operation as an insert as there is nothing to update since I don’t have this record.

Let’s say I pass last sync version now as 1.Below are the results.

Delete

USE CTDemoDb
GO
DELETE FROM dbo.Person where ID =4

Results from CT

A word on transactions

As I mentioned earlier, the version numbers are changed for each transaction and not for each operation. Let’s take below an example of running multiple queries under a single transaction.

BEGIN TRANSACTION
INSERT INTO [dbo].[Person]
([Id]
,[FirstName]
,[LastName]
,[DateOfBirth]
,[ModifiedDate])
VALUES
(5
,'Matt'
,'Dammon'
,'1982-12-7'
,getdate())

INSERT INTO [dbo].[Person]
([Id]
,[FirstName]
,[LastName]
,[DateOfBirth]
,[ModifiedDate])
VALUES
(6
,'Roger'
,'Moore'
,'1967-7-12'
,getdate())
COMMIT TRANSACTION

Results

If you notice the version number is only increased by one i.e. for one transaction.

Hope this helps you understand basic SQL server change tracking. In the next post, we will discuss on change tracking columns.