Change Tracking in SQL Server 2008

Before the Launching of SQL Server 2008, it was very difficult to track the changes made to the data in our tables. We had to build a custom solution to do the tracking using triggers on the tables being tracked and jobs to clean up all the extra data. But now SQL Server 2008 makes all this customization a thing of the past.

Change Tracking:

SQL Server 2008 has a new offering called Change Tracking that is great for building one-way or two-way synchronization applications and is designed to work with Synchronized services for ADO.NET. When it is turned on for a table, it will keep track of each DML event type and the keys of the row that were affected. This means that at any time, we can query to determine which rows have had an insert, update, and delete against them. You can retrieve the keys from Change Tracking and get the latest data from that table. It provides developers with a flexible foundation to build synchronized applications with .NET, but lacks Stored Procedure support or built-in monitoring tools like Replication Monitor.

Enabling Change Tracking on an existing table does not require any changes on the table schema. The only requirement is that the table must already have a primary key.

How Change Tracking Works

If we want to enable Change Tracking for our table then we need to first enable it at the database level. So once we have enabled it on a database level, then we can individually choose which tables we want to track and enable these tables. SQL Server creates an internal table that stores the net DML change information for that table; it means any DML operation occurring on that table will be recorded in this internal table. Since it's an internal table, we cannot directly query it, hence SQL Server has provided change tracking functions for that purpose. Gradually the data in the tracking tables will grow, so SQL Server also provides settings for doing automatic cleanup of these tables.


Functions of Change Tracking:

• CHANGETABLE:
  
  One of the most important and frequently used function of change tracking is "CHANGETABLE". It obtains tracking information for all changes to a table that have occurred since a specified version or the latest change tracking information for a specified row.
   
• CHANGE_TRACKING_MIN_VALID_VERSION:
  This function returns the minimum valid version for a table after the change tracking information is available or retained.
   
• CHANGE_TRACKING_CURRENT_VERSION:
  
  This version can be used the next time you enumerate the changes using CHANGETABLE. It obtains a version that is associated with the last committed transaction.
   
• CHANGE_TRACKING_IS_COLUMN_IN_MASK:
  
  The SYS_CHANGE_COLUMNS column in the table returned by the CHANGETABLE function contains information in binary format. The CHANGE_TRACKING_IS_COLUMN_IN_MASK function takes two parameters, the first is the table name and the second is column name and will return 1 or 0 depending on whether that column was updated or not.
   
• WITH CHANGE_TRACKING_CONTEXT:
  
  You can use this statement with your DML operations to differentiate the changes being done by your own application compared to others.

Enabling Change Tracking:

To enable Change Tracking on a table, you must first enable it for the database. ALTER DATABASE does this. The Change tracking function is found in the properties of database. In other words you must right-click on the database in which you want the change tracking function enabled and then click on properties.

Query:

ALTER DATABASE PatientInfo
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON , CHANGE_RETENTION = 5 DAYS)



After clicking on properties you will see a pop-up window where you will get the Change Tracking Property enabled by clicking on "True" and then "OK":



To disable Change Tracking, set it to OFF. All tables in the database must have change tracking disabled prior to disabling it for the database:

Query:

ALTER DATABASE PatientInfo
SET CHANGE_TRACKING = OFF

Similarly, to enable Change Tracking for a given table, you would execute something similar to this:

Query:

ALTER TABLE Patient
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

And of course to disable Change Tracking on the table we write:

Query:

ALTER TABLE Patient
DISABLE CHANGE_TRACKING