Many applications require the ability to track data changes in order to keep their local cache up to date. In the past, developers had to implement custom tracking solutions using triggers or timestamp columns, which could be expensive and introduce performance issues. However, SQL Server 2008 introduced a new feature called Change Tracking, which provides a lightweight and efficient way to track data changes.

Change Tracking is designed to work with Sync Services for ADO.NET and is great for building one-way or two-way synchronization applications. It allows applications to track the net DML (INSERT, UPDATE, and DELETE) changes that occur on a table, so that they can refresh themselves with just the changed dataset.

Let’s take a closer look at how to configure and enable Change Tracking in SQL Server.

Step 1: Create a table

First, we need to create a table in the database where we want to enable Change Tracking. For example, let’s create a table named “tblEmployee” with columns for EmployeeID, ManagerID, FirstName, LastName, and Gender.

CREATE TABLE [dbo].[tblEmployee]
(
      [EmployeeID] [int] NOT NULL,
      [ManagerID] [int] NULL,
      [FirstName] [varchar](20) NOT NULL,
      [LastName] [varchar](20) NOT NULL,
      [Gender] [bit] NULL,
      CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED
      (
          [EmployeeID] ASC
      )
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 2: Enable Change Tracking on Database

Before enabling Change Tracking on a table, we need to enable it at the database level. This can be done using the following SQL code:

ALTER DATABASE [DatabaseName]
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 3 DAYS)

Here, we set the “Auto Cleanup” option to ON, which means that tracked changes will be retained for 3 days only. You can adjust the retention period based on your project requirements, but it’s important to find a balance between retaining enough change information and not consuming too much space on the database server.

Step 3: Enable Change Tracking at Table Level

Next, we need to enable Change Tracking for the specific table we want to track. By default, all columns within the table will be tracked, but we can also specify specific columns if needed.

ALTER TABLE [dbo].[tblEmployee]
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

Step 4: Verify Change Tracking Configuration

After enabling Change Tracking, it’s important to verify that it has been enabled correctly. We can do this by querying the system tables in SQL Server.

SELECT DB_NAME(database_id) AS NAME, 
       retention_period_units, 
       retention_period_units_desc 
FROM sys.change_tracking_databases

SELECT t.name AS TableName
FROM sys.change_tracking_tables ctt
JOIN sys.tables t ON ctt.object_id = t.object_id

The first query retrieves information about the database-level Change Tracking configuration, while the second query shows the tables for which Change Tracking has been enabled.

Step 5: Retrieve Changed Information

To retrieve the tracked changes, we can use system functions provided by SQL Server. For example:

SELECT CHANGE_TRACKING_CURRENT_VERSION ()
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('tblEmployee'))
SELECT * 
FROM CHANGETABLE (CHANGES tblEmployee, 0) AS CT
ORDER BY SYS_CHANGE_VERSION

The first function, CHANGE_TRACKING_CURRENT_VERSION, returns the current version number at the database level. The second function, CHANGE_TRACKING_MIN_VALID_VERSION, gives the minimum version after which the change information has been retained for a specific table. The third query retrieves the actual changed information using the CHANGETABLE function.

Step 6: Perform DML Operations

Now, let’s perform some DML operations on the tracked table to see how Change Tracking captures the changes. For example, we can insert, update, or delete records in the “tblEmployee” table.

INSERT INTO [dbo].[tblEmployee] VALUES (1, NULL, 'John', 'Doe', 1)
UPDATE [dbo].[tblEmployee] SET FirstName = 'Jane' WHERE EmployeeID = 1
DELETE FROM [dbo].[tblEmployee] WHERE EmployeeID = 1

Step 7: Retrieve Changed Information

After performing the DML operations, we can retrieve the tracked changes again to see the updated information.

SELECT CHANGE_TRACKING_CURRENT_VERSION ()
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('tblEmployee'))
SELECT * 
FROM CHANGETABLE (CHANGES tblEmployee, 0) AS CT
ORDER BY SYS_CHANGE_VERSION

By comparing the results with the previous retrieval, we can see the changes that have been captured by Change Tracking.

Conclusion

Change Tracking in SQL Server is a powerful feature that allows applications to track data changes efficiently. By enabling Change Tracking at the database and table level, developers can easily retrieve the changed information and keep their applications synchronized with the central database. This eliminates the need for complex custom tracking solutions and improves performance.

Remember to carefully configure the retention period for tracked changes to balance space usage and the ability to capture changes effectively. Additionally, consider using version numbers or other mechanisms to detect synchronization issues in case of data loss.

With Change Tracking integrated into SQL Server, developers can focus on building robust and efficient applications without worrying about implementing custom tracking solutions.