Change Tracking in SQL Server 2008


Configuring Change Tracking in Tables

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:

CT_img_2.bmp

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.

INSERT INTO dbo.tblEmployees

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

CT_img_3.bmp

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:

CT_img_4.bmp

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.

CT_img_5.bmp

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;

CT_img_6.bmp

 
Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

One Response to “Change Tracking in SQL Server 2008”

  1. Nice article.

    I want to know whether SNAPSHOT ISOLATION is to be added in all stored procedures that perform INSERT/UPDATE.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |