Skip to content

SQL Server – Change Tracking (CT)

September 2, 2013

SQL Server – Change Tracking (CT)

Introduction

Have you ever tried to track the changes (DML changes like Insert, Delete and Update) which were done by you or your team members?

If you have not, let’s play with the data by using one of the great features of SQL Server 2008.  

Background

Prior to SQL Server 2008, developers had to create some custom tracking solutions using DML Trigger and additional tables to track the data which we have modified.

  • DML Triggers: These are the part of our transaction which contains the DML by which it is triggered. As we all know that, triggers are very expensive and we are using them in our transaction, the execution time will increase so that the performance of our project will be affected  
  • Additional tables: By running the above DML triggers, we are able to track the data. But there is nothing to store these changes. To store this changed data, we need to create these additional tables. These tables will have similar columns as the tables which we need to track.  

Drawbacks

  • Takes much time to develop/create DML triggers and additional tables    
  • Performance hit 
  • Very complex process  

SQL Server 2008

To overcome the above drawbacks, SQL Server 2008 introduced powerful and efficient tracking mechanisms called‘Change Tracking (CT)’ and Change data Capture (CDC)  

In this article Change Data Capture (CDC) is out of scope. We may discuss about this in the coming articles.

Note: Since I am a lazy fellow I am not going to say Change tracking every time in this article. But I will just say CT.  

CT is a new feature in SQL Server 2008 which allows us to track information about changes that we have made to tables in which CT is enabled. 

When this is turned on for a table, it will keep track of each DML operation and the keys of the row that was affected. This means that at any time, we can query to find out which rows in our table(s) were inserted/deleted/updated.

Advantages:

  • DML Triggers and additional tables are no more required  
  • Efficient & fast    
  • Easy to track the data    
  • Minimal Disk Space Costs    
  • Packaged Functions available to query the data    
  • Auto Clean Up of side table data 
Advertisements

From → Tech

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: