Change Tracking in SQL Server 2008

After configuring Change Tracking on the Server the next task is to enable it for the table.      

Tacking is done by making a note of the primary key of the row that changed along with a version number  (NB change tracking on a table requires it to have  a primary key). Once a database is enabled for change tracking a version number is instituted, which allows ordering of operations. You can also optionally track which columns changed.

ALTER TABLE [dbo].[tblName]

ENABLE CHANGE_TRACKING

WITH(TRACK_COLUMNS_UPDATED = ON)

You can get the information about tracking tables by querying SELECT * FROM sys.change_tracking_tables

To disable   Change Tracking for a table   use the following T-SQL command.  

ALTER TABLE [dbo].[tblName]

DISABLE CHANGE_TRACKING

Enable Change Tracking for a table using SSMS by selecting table Properties > Change Tracking , as shown in the below screenshot:

Implementation Change Tracking

After a DML operation is made on database, first the relevant operation will be performed on  the table and then a row is added to an internal change tracking table. There is one internal table for each user table that uses change tracking.

Thus, change tracking is based on logging information in data tables to capture what is happening.

Let us first create a table with multiple columns with a primary key in a database in which change tracking is enabled.

CREATE TABLE [dbo].[tblEmployees](

     [ID] [int] IDENTITY(1,1) NOT NULL,

     [Name] [varchar](30) NULL,

     [Salary] [numeric](7, 2) NULL,

     [Location] [varchar](5) NULL,

 CONSTRAINT [PK_tblEmployees] PRIMARY KEY CLUSTERED

(

     [ID] ASC

)

) ON [PRIMARY]

Then enable the change tracking on the tblEmployees table with Column update on.

ALTER TABLE [dbo].[tblEmployees]

ENABLE CHANGE_TRACKING

WITH(TRACK_COLUMNS_UPDATED = ON)

Next we will perform some sample operations, first – Insert.

(Name,Salary,Location)

VALUES

(‘Dinesh’,10000,’SRL’),

(‘Asanka’,15000,’UK’),

(‘John’,17500,’UK’)

The above statement will insert three records. (NB: This is a new feature in SQL Server 2008 where you can insert multiple records using single INSERT statement.)

There is a change tracking function to retrieve changed data called CHANGETABLE.

SELECT * FROM CHANGETABLE

(CHANGES dbo.tblEmployees,0) as CT

The CHANGETABLE function has two parameters.  The first parameter is to supply the table name. CHANGES is a key word you have to include to retrieve the changes. Another option VERSION will be discussed later.

The second parameter is the version number. The version number will be maintained across the database. For a single operation on any table which has Change Tracking enabled the version number will be incremented. For example, after enabling Change Tracking, the version number will be 0 and after inserting records in table A the version number will be 1 and performing any updates will result in a version number of  2.  

 If you pass 0 for a version parameter, you will get all the changes that were done after the 0 version which means all the changes after the enabling the Change Tracking. The important thing to note here is that you will get the net changes,  which is very handy for data warehousing.

The below is the output you receive from running the above query.

SYS_CHANGE_VERSION:  Version value that is associated with the last change to the row

SYS_CHANGE_CREATION_VERSION: Version values that are associated with the last insert operation.

SYS_CHANGE_OPERATION: Specifies the type of change, U = Update, I = Insert, D = Delete

SYS_CHANGE_COLUMNS: Lists the columns that have changed since the baseline version. This column has a value only for Update operation. Also when all columns are updates, still this column will be null.

SYS_CHANGE_CONTEXT: Change context information that you can optionally specify by using the WITH clause as part of an INSERT, UPDATE, or DELETE statement.

ID : The primary key of the base table which will allow you to join Change table with the base table.

After doing some operations on the tblEmployees and then running the below T-SQL

SELECT * FROM CHANGETABLE

(CHANGES dbo.tblEmployees,0) as CT

The following output will be generated:

You can see that though you have done several changes, you will receive only the net changes. Hence only the Inserts and deletes for the version 0 are shown.

SELECT * FROM CHANGETABLE

(CHANGES dbo.tblEmployees,2) as CT query will give you following results.

There are a few things to note here.  The  second result set has only eight records while first has 10 records. This is because Id 4 and 5 records were not updated after version 2. Also you can see that SYS_CHANGE_COLUMNS has some values and SYS_CHANGE_OPERATION values have changed to U.

Update the SYS_CHANGE_CONTEXT Column

As before, there is a column called SYS_CHANGE_CONTEXT and following example  shows   how to update that column so it can be used as a audit feature.

DECLARE @originator_id varbinary(128);

SET @originator_id = CAST(‘Sample Application’ AS varbinary(128));

WITH CHANGE_TRACKING_CONTEXT (@originator_id)

    UPDATE tblEmployees

      SET Salary = 50000

      WHERE ID = 1

Get All Rows With Latest Versions

The below query will return   all the columns and their latest versions. You can still use Inner join tblEmployees with CHNAGETABLE (CHANGES dbo.tblEmployees, 0) but this query is better when there are a large number of records.

SELECT emp.[ID],

        emp.Name,

        emp.Salary,

        emp.Location,

       ct.SYS_CHANGE_VERSION,

       ct.SYS_CHANGE_CONTEXT

FROM dbo.tblemployees AS emp

CROSS APPLY CHANGETABLE

    (VERSION dbo.tblEmployees, ([ID]), (emp.[ID])) AS ct;

 

Continues…

ASK A QUESTION