Change Tracking in SQL Server

Introduction

In this article, we will learn about 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 In SQL Server

Change tracking captures rows in a table that were changed but did not capture the changed data. In other words, CT enables applications to determine the changed rows with the latest row data. So, Change tracking has limited features. However, change tracking is helpful for applications that do not require historical data, reducing 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 case of data warehousing, which requires data to be taken from multiple data sources. If it is needed 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 tracking that keeps the information. Change tracking removes information older than this time period. If an application obtains changes at considerable intervals, the results obtained from change tracking might be incorrect because the changed information has likely been removed. To avoid this inaccurate result in the changing 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.

The database compatibility level must be set to 90 or greater to use change tracking. 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 the table we want to track. Change tracking maintains the information for all rows affected 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 database, it must be disabled for all database tables.

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 determine the latest data for tracked rows, join the source table with the tracked table.

Run the following script to determine the tables for which change tracking has been enabled.

--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 used in change tracking. It returns the change tracking information for a specific row or a collection of rows. It takes two arguments:

• "CHANGE / VERSION" along with the 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 columns since baseline version.

  The value is null when any one of the following conditions is true.
  • Operation is inserted or deleted.
  • The column change track is not enabled.
  • All nonprimary keys were updated in one operation.

• SYS_CHANGE_CONTEXT: Change context information that you can optionally specify 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 the change tracked is enabled.

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Customer'))

CHANGE_TRACKING_CURRENT_VERSION - This function returns the current version after the 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 returned values SYS_CHANGE_COLUMNS (CHANGETABLE function). This function will return 0 or 1 whether the specified column is part of the change_column list. This function takes two arguments.

• Column id- Column Id is found using the "COLUMNPROPERTY" function.

• Change_Column- Change_Column 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 application and 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;

Conclusion

This article taught us about change tracking with its various functions and code examples in SQL Server.