Change Tracking in SQL Server 2008

All Changes After A Specific Version

This is the most common reason for using Change tracking. This query will return all the changes after a specific version.

DECLARE @last_sync_version bigint;

SET @last_sync_version = 5;

SELECT emp.Name,

          emp.Location,

          emp.Salary,

          ct.[ID],

          ct.SYS_CHANGE_VERSION,

          ct.SYS_CHANGE_OPERATION,

          ct.SYS_CHANGE_COLUMNS,

          ct.SYS_CHANGE_CONTEXT

FROM CHANGETABLE (CHANGES dbo.tblEmployees, @last_sync_version) AS ct

LEFT OUTER JOIN dbo.tblEmployees AS emp

ON emp.[ID] = ct.[ID]

The below output be generated from this query.

CT_img_7.bmp

If you are running a data warehouse, you can pass the last synchronised version to retrieve the relevant data for the data warehouse and then store the latest version so that you can pass that value during next cycle of synchronization.

Change Tracking Functions

There are three additional Change Tracking functions:

CHANGE_TRACKING_IS_COLUMN_IN_MASK

When a record is updated,  SYS_CHANGE_COLUMNS is updated with a mask value.  CHANGE_TRACKING_IS_COLUMN_IN_MASK will allow you to unmask the column name.  This function requires two parameters, the first parameter is the column Id and the second is the MASK value. This function will inform you  whether the given column was updated or not. You cannot get the list of columns from this function.   

The following query will list all the rows which were updated after version 3 with an indication as to whether a  particular column has changed.

SELECT ID,

SYS_CHANGE_OPERATION,

SYS_CHANGE_COLUMNS,

CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(‘tblEmployees’), ‘Name’, ‘ColumnId’), SYS_CHANGE_COLUMNS)IsNameChanged ,

CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(‘tblEmployees’), ‘Location’, ‘ColumnId’), SYS_CHANGE_COLUMNS)IsLocationChanged ,

CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(‘tblEmployees’), ‘Salary’, ‘ColumnId’), SYS_CHANGE_COLUMNS)IsSalaryChanged

FROM CHANGETABLE

(CHANGES dbo.tblEmployees,3) as CT

Where SYS_CHANGE_OPERATION =’U’

CT_img_8.bmp

Note that for ID = 1 row though the SYS_CHANGE_COLUMNS is null all the columns have changed.

CHANGE_TRACKING_CURRENT_VERSION

CHANGE_TRACKING_CURRENT_VERSION returns a version that is associated with the last committed transaction.  Eg:
SELECT CHANGE_TRACKING_CURRENT_VERSION ()

CHANGE_TRACKING_MIN_VALID_VERSION

This function   returns the minimum version that is valid for use in obtaining change tracking information for  the specified table. Eg:
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(‘tblEmployees’));

Comparison of Change Tracking and Change Data Capture in Tables

Feature

Change Tracking

Change Data Capture

Synchronous

Yes

No

Requires SQL Agent

No

Yes

Forces full logging of some bulk operations

No

Yes

Prevents log truncation

No

Yes, until log records harvested

Requires snapshot isolation

Recommended

No

Requires separate tables to store tracking data

Yes

Yes

Requires primary key

Yes

Not by default

Allows placement of tracking tables

No

Yes

Potential for space consumption issues

Some

Lots

Automatic cleanup process

Yes

Yes

Restrictions on DDL

Yes

No







Permission required to enable

Dbcreator

Database owner

Support Editions

All Editions

Enterprise

The main point to stress here is   the availability of Change Tracking among all the SQL Server editions. Change tracking feature is available even in the SQL Server express.

Conclusion

Change Tracking is light-weight option when compared to CDC available in all editions of SQL Server. You can download the script associated with the Change Tracking and if you want to clarify any questions fell free to send a mail to dineshasanka@dbfriend.net

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 |