SQL - Change tracking

Most of the applications which I worked till now, had one or the other requirement to handle auditing feature to track the changes on data. There are different ways to achieve this. Very often we use triggers to update another table whenever there is a change on primary table. Or handle it on the stored proc which is used for inserting or updating data.


Now, with Sql 2008, this made very easy. The new feature introduced on SQL 2008 “Change tracking” which enables change tracking without any coding required.

First, to enable this change tracking, we need to update the setting on DB

After that we need to enable this on the table whichever needs to be tracked.

Once the table is enabled with tracking, you will be able to track the changes by version.

We can get all the changes by using function called CHANGETABLE. sample use of function will look like below.

SELECT * FROM CHANGETABLE


(CHANGES dbo.Employees,0) as CT


This query will provide details with SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT, ID columns.

Take a look at this article http://www.sql-server-performance.com/articles/audit/change_tracking_2008_p1.aspx  which talk about this article in detail.