SQL Server 2008 Change Tracking

SQL Server 2008 introduces change tracking, a lightweight solution that provides an efficient change tracking mechanism for applications. Different types of applications have different requirements for how much information they need about the changes. Applications can use change tracking to answer the following questions about the changes that have been made to a user table:

· What rows have changed for a user table?

o Only the fact that a row has changed is required, not how many times the row has changed or the values of any intermediate changes.

o The latest data can be obtained directly from the table that is being tracked.

· Has a row changed?

o The fact that a row has changed and information about the change must be available and recorded at the time that the change was made in the same transaction.

How to do?

Use to enable or disable change tracking for the database.

To enable change tracking, you must have permission to modify the database.

Setting the value to True sets a database option that allows change tracking to be enabled on individual tables.

You can also configure change tracking by using ALTER DATABASE.

Retention Period

Specifies the minimum period for keeping change track information in the database. Data is removed only if the Auto Clean-Up value is True.

The default value is 2.

Retention Period Units

Specifies the units for the Retention Period value. You can select Days, Hours, or Minutes. The default value is Days.

The minimum retention period is 1 minute. There is no maximum retention period.

Auto Clean-Up

Indicates whether change tracking information is automatically removed after the specified retention period.

Enabling Auto Clean-Up resets any previous custom retention period to the default retention period of 2 days.

For any database that uses change tracking, be aware of the following:

· To use change tracking, the database compatibility level must be set to 90 or greater. If a database has a compatibility level of less than 90, you can configure change tracking. However, the CHANGETABLE function, which is used to obtain change tracking information, will return an error.

· Using snapshot isolation is the easiest way for you to help ensure that all change tracking information is consistent. For this reason, we strongly recommend that snapshot isolation be set to ON for the database.

Change tracking must be enabled for each table that you want tracked. When change tracking is enabled, change tracking information is maintained for all rows in the table that are affected by a DML operation.

The following example shows how to enable change tracking for a table by using ALTER TABLE:

ALTER TABLE Person.Person

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON);

When the TRACK_COLUMNS_UPDATED option is set to ON, the SQL Server Database Engine stores extra information about which columns were updated to the internal change tracking table. Column tracking can enable an application to synchronize only those columns that were updated. This can improve efficiency and performance. However, because maintaining column tracking information adds some extra storage overhead, this option is set to OFF by default.

Change Tracking Settings

How to retrieve the changes ?

CHANGETABLE(CHANGES …) function

This rowset function is used to query for change information. The function queries the data stored in the internal change tracking tables. The function returns a results set that contains the primary keys of rows that have changed together with other change information such as the operation, columns updated and version for the row.

CHANGETABLE(CHANGES …) takes a last synchronization version as an argument. The last synchronization version is obtained using the @last_synchronization_version variable, as shown in the examples in this topic. The semantics of the last synchronization version are as follows:

· The calling client has obtained changes and knows about all changes up to and including the last synchronization version.

· CHANGETABLE(CHANGES …) will therefore return all changes that have occurred after the last synchronization version.

Reference :MSDN – Microsoft®