Click here to Skip to main content
15,868,306 members
Articles / Database Development / SQL Server

SQL Server Change Tracking on Table (Without Triggers)

Rate me:
Please Sign up or sign in to vote.
4.96/5 (19 votes)
6 Mar 2012CPOL3 min read 108.9K   60   20
SQL-Server change tracking on a 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

SQL
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.

SQL
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.

SQL
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.

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

Identifying the changes:

SQL
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.

SQL
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:

SYS_CHANGE_VERSION

SYS_CHANGE_CREATION_VERSION

SYS_CHANGE_OPERATION

SYS_CHANGE_COLUMNS

SYS_CHANGE_CONTEXT

EmployeeID

FirstName

LastName

Phone1

26

27

U

0x0000000004000000

NULL

E001

Santosh

Poojari

12121212

27

27

U

0x0000000004000000

NULL

E002

Karan

Shah

21212121

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:

SQL
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:

SYS_CHANGE_VERSION

SYS_CHANGE_CREATION_VERSION

SYS_CHANGE_OPERATION

SYS_CHANGE_COLUMNS

SYS_CHANGE_CONTEXT

EmployeeID

FirstName

LastName

Phone1

26

27

U

0x0000000004000000

NULL

E001

Santosh

Poojari

12121212

27

27

U

0x0000000004000000

NULL

E002

Karan

Shah

21212121

28

28

I

NULL

NULL

E006

S

G

555555

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
Im is a Senior Software Developer working with a software company in Mumbai(India). He has overall 5.5 years of experience in .net technology. He has knowledge in C# 3.0, SQL Server 2005, SQL Reporting service, Enterprise Library 3.0, WCSF & Windows Workflow Foundation.

He has a hands on cutting edge tool like MS Visio, Rational Rose, Borland together 2006 & CruiseControl.Net


Currently his area of interest is on LINQ and Sharepoint.

He is MCPD-EA Certified.

Comments and Discussions

 
GeneralGood knowledge sharing... Pin
Muhammad Idrees GS6-Mar-12 19:00
Muhammad Idrees GS6-Mar-12 19:00 
QuestionThanks for the Great Article Pin
Member 19107305-Mar-12 12:46
Member 19107305-Mar-12 12:46 
AnswerRe: Thanks for the Great Article Pin
Gautam Sharma6-Mar-12 7:07
Gautam Sharma6-Mar-12 7:07 
Thanks tanveer for showing the interest by reading and article... This system is mostly used in replication process, where he whole data is been transferred from sql to another form of database. Instead of replication the entire data, only the changes are updated.. and new records are appended in the destination database.


The Data history is not yet kept in this mechanism. Im anyways researching on how to keep the track of the data history.. but more than that im wondering the scenario in which the data history would be required.

Most of the time the database architecture design table Custom history table that would be used regularly. But the change tracking is mostly used in replication process.
Happy coding

QuestionFormatting Pin
DaveAuld1-Mar-12 12:59
professionalDaveAuld1-Mar-12 12:59 
AnswerRe: Formatting Pin
Gautam Sharma5-Mar-12 12:52
Gautam Sharma5-Mar-12 12:52 
QuestionGood Reference Pin
santosh poojari29-Feb-12 20:55
santosh poojari29-Feb-12 20:55 
AnswerRe: Good Reference Pin
Gautam Sharma29-Feb-12 21:03
Gautam Sharma29-Feb-12 21:03 
GeneralMy vote of 5 Pin
Patrick Harris29-Feb-12 18:15
Patrick Harris29-Feb-12 18:15 
QuestionThanks for the effort, but... Pin
Dewey29-Feb-12 16:04
Dewey29-Feb-12 16:04 
AnswerRe: Thanks for the effort, but... Pin
Gautam Sharma29-Feb-12 21:01
Gautam Sharma29-Feb-12 21:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.