Change Tracking in SQL Server

SQL Server 2008 introduces two new tracking features that enable applications to determine the DML changes (insert, update, and delete operations).

1. CDC ( Change Data Capture)
2. CT (Change Tracking)

Change tracking captures rows in a table were changed, but do not capture the data that was changed. In other words CT enables applications to determine the rows that have changed with the latest row data. So, Change tracking has limited features. However, change tracking is useful for those applications that do not require the historical data, so it reduces storage overhead because the changed data is not being captured.

Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications. Let's take an example of Data warehousing. In the case of data warehousing, that requires data to be taken from multiple data sources. If it is required to extract data from a large table, then it is necessary to extract only that data that has changed. In this case, Change Tracking is a lightweight solution to extract modified data from one SQL Server to another SQL Server.

Configuring Change Tracking

Before change tracking, it must be enabled at the database level.

--Script for enable change tracking at database level.
ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);



We can specify the CHANGE_RETENTION and AUTO_CLEANUP options when enabling change tracking and we may change the values at any time after change tracking is enabled.

The change retention value is specified in the time period for which tracking keeps the information. Change tracking removes information older than this time period. If an application obtain changes at large intervals, the results that obtain from change tracking, it might be incorrect because information that has change has likely been removed. To avoid this type of incorrect result in change track, we can use the CHANGE_TRACKING_MIN_VALID_VERSION (system function) to determine whether the interval is appropriate for synchronizations.

We can use the AUTO_CLEANUP option to enable or disable the cleanup task which removes old change tracking information from the system.

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, we may configure change tracking, but the CHANGETABLE function used to obtain change tracking information, would return the error.

Change tracking must be enabled for table for which we want to track. Change tracking maintains the information for all rows effected by DML statements.

--Script for enable change tracking for "Customer" Table.
ALTER TABLE dbo.Customer
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);



When the TRACK_COLUMNS_UPDATED option is set to ON, the Database Engine stores information about which column was updated to the internal change tracking table. This option is set to OFF by default.

Disabling Change Tracking

To Disable Change tracking for a data base, it must be disabled for all tables of that database.

ALTER TABLE dbo.Customer
DISABLE CHANGE_TRACKING;

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = OFF;

Difference between Change Data Capture and Change Tracking

The following table lists the feature differences between change data capture and change tracking.

Working with Change Tracking

Change Tracking records DML statements (i.e. Insert, Update and Delete). The value of the primary key column is only information from the tracked table which is recorded as the change information. This value or column helps us to identify the row which has been changed. To deterrmine the latest data for rows which are tracked, join the source table with the tracked table.

To determine the tables for which change tracking has been enabled, run the following script.

--Script for find CT enabled database.
SELECT * FROM sys.change_tracking_databases

--Script for find CT enabled table.
SELECT * FROM sys.change_tracking_tables

Change tracking functions

CHANGETABLE - This is one of the most important functions that used in change tracking. It returns the change tracking information either for a specific row or a collection of rows. It takes two arguments:

• "CHANGE / VERSION" along with table name

• Previous Sync Version

SELECT * FROM CHANGETABLE
(CHANGES Customer,0) as CT ORDER BY SYS_CHANGE_VERSION



This function returns a couple of columns.

• SYS_CHANGE_VERSION: Version number in which row was changed.

• SYS_CHANGE_CREATION_VERSION: Version of last insert operation.

• SYS_CHANGE_OPERATION: Type of operation.
  

  • U = Update
  • I = Insert
  • D = Delete

• SYS_CHANGE_COLUMNS: List of column since base line version.
  
  The value is null when any one of following condition is true.
  

  • Operation is insert or delete.
  • Column change track is not enabled.
  • All non primary key were updated in one operation.

• SYS_CHANGE_CONTEXT: Change context information that you can optionally specify by using the WITH clause as part of an INSERT, UPDATE, or DELETE statement.

• <Primary Key of Table>: The primary key values for the tracked table.

CHANGE_TRACKING_MIN_VALID_VERSION - This function returns a minimum valid version of the table after change tracked is enabled.

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Customer'))

CHANGE_TRACKING_CURRENT_VERSION - This function return current version after last successful transaction. This will return "NULL", there is no change tracking enabled.

CHANGE_TRACKING_IS_COLUMN_IN_MASK - This enables an application to determine whether the specified column is included in the values that are returned for SYS_CHANGE_COLUMNS (CHANGETABLE function).This function will return 0 or 1 whether specified column is part of change_column list or not. This function takes two arguments.

• Column id: Column Id which is find using "COLUMNPROPERTY" function.

• Change_Column: it Is the binary data from the SYS_CHANGE_COLUMNS column of the CHANGETABLE data

SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Customer'), 'AddressLine1', 'ColumnId'),Null)

WITH CHANGE_TRACKING_CONTEXT - If we want to differentiate between changes done by our own application and changes done by other applications.

DECLARE @last_sync_version int;
set @last_sync_version = 5
DECLARE @address varbinary(128);
SET @address = CAST('AddressLine1' AS varbinary(128));
WITH CHANGE_TRACKING_CONTEXT (@address)
    UPDATE Customer
    SET AddressLine1 ='Address - 5'
    WHERE CustomerID = 3
-- The change now has an associated change context.
SELECT c.CustomerID, c.SYS_CHANGE_CONTEXT
FROM CHANGETABLE(CHANGES Customer, @last_sync_version) AS c;