SQL Server Change Tracking on Table (Without Triggers)

Introduction

Before you start reading this article. Just wanted to tell you guys that I don’t write articles much, this is just an attempt after years. So please excuse the paraphrases if they are not well articulated. My objective is to just give you a bit of idea about some of the hidden mechanism of SQL (or maybe I was not aware of this feature before researched and implemented this.) named as Change Tracking.

Most of the time we have experienced that triggers are not the best solution to find out whether any records are modified in the table. Since event of triggers are tightly bind with the insert update or delete action. Soon after implementation you might experience issues in future releases. There was never a straightforward solution to track the changes that are happing on the records, and above this the mind goes on toss when you want to track it live. And this becomes a biggest challenge when you have a limitation that and the best practice words like “DO NOT USE TRIGGERS”. 

Well here I’m going to introduce you with a workaround of trigger, or maybe I should put it in this words. How to keep a track of the records those are modified or created in the database.

Overview

Change Tracking enables an application to keep track of all changes made by the user and this is an inbuilt method.

This plays a very important role when you are building a real time replication tool that synchronies data from one database to another type of database that type can be oracle or Mangodb.

Configuring / Enabling CT (Change Tracking) on the database

Alter Database <DatabaseName> 
set change_tracking = on
(change_retention = 2 days, auto_cleanup = on);

Note: once the CT is turned on, on the database level it does not enables the CT of all the tables of the database. The CT mast to be enabled on the tables individually

Let me explain a bit here: the functionality of CT is to keep the track of bunch of record changes with the version number. If you look carefully the Change_retention = 2 will actually keep the latest version number of the record that is been modified in the system for 2 days.

Configuring Table for CT (Change Tracking)

For this article I have created the sample table named Employee with some sample values, and would consider employee table as the basis of current article going forward.

Create table Employee 
(
EmployeeID nvarchar(10) primary key,
FirstName nvarchar(100),
LastName nvarchar(100),
Phone1 nvarchar(10)
)
insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E001','Santosh','Poojari','1111111111')
insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E002','Karan','Shah','2222222222')
insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E003','Vineesh','Padmanabhan','3333333333')
insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E004','Gautam','Sharma','4444444444')

Since the database is configured with the CT. we can proceed and enable the CT on the table. Below is the code how to enable to CT on Employee table that already have records.

ALTER TABLE Employee ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

Tracking the Changes made in the Table

Now let's change the phone numbers of two of the employees in table and see wether CT is able to return the Employee ID from the changes are made.

update employee set Phone1 ='12121212' where EmployeeID ='E001'
update employee set Phone1 ='21212121' where EmployeeID ='E002'

Identifying the changes:

SELECT ISNUll(pn.EmployeeID,0) as EmployeeID 
from changetable(changes employee, 1) as ct
INNER JOIN employee pn on pn.EmployeeID = CT.EmployeeID
WHERE SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'
Resultset : 

EmployeeID
----------
E001
E002
(2 row(s) affected)

As you can see in the above box that the CT is able to give you the employee ID of the modified records in the employee table.

Now let me modify the code little bit and show the entire column info that is been returned in the resultset.

SELECT *
from changetable(changes employee, 1) as ct
INNER JOIN employee pn on pn.EmployeeID = CT.EmployeeID
WHERE SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'

Resultset:

The SYS_CHANGE_OPERATION column provides the information what happened to this record “U” stats that it was modified.

Let's do and insert and see what the resultset would look like:

insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E006','S','G','555555')
GO
SELECT *
from changetable(changes employee, 1) as ct
INNER JOIN employee pn on pn.EmployeeID = CT.EmployeeID
WHERE SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'

Resultset:

As you can see the column SYS_CHANGE_OPERATION with new employee record has the new incremented version number 28.

So whenever there are any changes that are done on CT enabled tables. A new version number is created and will be retained in the change tracking system table for 2 days (as configured).

You can write to me if there are any further queries. You can also refer the tutorial on MSDN by clicking here.