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.

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’

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

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