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




Array

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 |