SQL Server Advent Calendar 17 – Change Tracking

  • Article

Day 17 of my virtual advent calendar, about stuff I like in SQL Server 2008..

Today’s topic is the confusingly named change tracking i.e. nothing to do with Change Data Capture (CDC) I mentioned in my last post. CDC is only in enterprise edition only and is there to primarily support population of data warehouses while Change Tracking  is a developer orientated tool designed to support synchronisation across different platforms and is therefore included in all editions of SQL Server 2008.

Like CDC you need to turn it on either via the Management Studio (which you won’t have if your using Express) or via T-SQL..

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

The process relies on version numbers and every time a change is made this gets incremented and then this is used in various functions to get at the data..

  • CHANGE_TRACKING_CURRENT_VERSION()  represents the version of the last committed transaction. Before any application can obtain changes for the first time, the application must send a query to obtain the initial data and the synchronization version. The application must obtain the appropriate data directly from the table, and then use CHANGE_TRACKING_CURRENT_VERSION() to obtain the initial version like this..

@CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION()

  • CHANGE_TRACKING_MIN_VALID_VERSION() tells you the earliest version ID that is held (based on the retention period you define).  So you would use this to check that you have the right version before getting the changed data.
  • CHANGETABLE(CHANGES [source Table], @Synchronisation version) . Note you will want to RIGHT join this as a table to the source table to get the data that actually changed as this function merely tells you which row changed. I mention right join because the row may have been deleted.

This is more a developer thing and so the virtual lab for this is on MSDN, and there is also more about it in Books On-Line here. 

Technorati Tags: SQL Server 2008,Change Tracking,Occasionally Connected,Synchronisation