SQL Change Tracking in SQL Server 2008

What is Change Tracking?

Change tracking is a new feature in SQL Server 2008 that allows applications to query for information about the changes that have been made to user tables since a previous point in time. Change tracking is intended to be used as a building-block used by synchronization components or applications.

In order to enable change tracking at the table level, one needs to enable it at the table level and then needs to be enabled at the database level. The default value change tracking is not enabled for a database

Enabling Change Tracking at the database level

1. Open the SQL Server Management Studio and connect to SQL Server 2008

2. Open Object Explorer and expand Databases.

3. Select the particular database where the change tracking needs to be enabled. Right click the database and select properties to launch the properties dialog.

4. Navigate to “Change Tracking” page and change the value of change tracking from false to true.

5. Enter the required values for Retention Period, Retention Period Units and Auto Cleanup.

- Retention Period: Change information will be retained for atleast the time period that is specified by the retention period and retention period units.

- Retention Period Units: The units of the Retention Period Days or Months or Years.

- Auto Cleanup: Automatically clean up the change tracking information by using the specified retention period. If this value is false, change tracking information automatically continues to grow.

Click ‘Ok ‘ to execute the action or Click ‘Script’ to generate the T-SQL for this action.

Enabling Change Tracking at the table level:

1. Open the SQL Server Management Studio and connect to SQL Server 2008

2. Open Object Explorer and expand Databases and select the database and expand the tables under it.

3. Select the particular table where the change tracking needs to be enabled. Right click the table and select properties to launch the properties dialog.

4. Navigate to “Change Tracking” page and change the value of change tracking from false to true.

5. Enter the required value for the track columns updated field. This field is used to indicate the columns which are changed by UPDATE operation and also indicates that row has changed.

Click ‘Ok ‘ to execute the action or Click ‘Script’ to generate the T-SQL for this action.

Note: Change tracking is supported only on the tables having primary key.

In case if one wants to view the records which are changed after enabling the change tracking, there is no corresponding for it. But using T-SQL, one can find it easily.

In order to find the rows changed on a table, we needs to use CHANGETABLE(CHANGES …)and CHANGE_TRACKING_CURRENT_VERSION().

T-SQL for viewing the records changed

-- Assuming dbo.t is the table to view the records changed in it

DECLARE @sync_version int;

DECLARE @last_sync_version int;

SET @sync_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Obtain incremental changes using the sync version obtained last time

SELECT

    P.<columnname>,

    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT

FROM

    <tablename> AS P

RIGHT OUTER JOIN

    CHANGETABLE(CHANGES dbo.d, 0) AS CT

ON

    P.c1 = CT.c1

CHANGETABLE() returns the table containg the below columns :

SYS_CHANGE_OPERATION – Type of DML operation on a row.

SYS_CHANGE_COLUMNS – Columns changed from a baseline version

SYS_CHANGE_CONTEXT – Optionally specified with the WITH clause

SYS_CHANGE_VERSION – Current change version associated with a row.