SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 1

By: Ameena Lalani   |   Comments (2)   |   Related: > Change Data Capture

Problem

Change Data Capture (CDC) and Change Tracking (CT) were both introduced in SQL Server 2008 for data tracking.  While CDC was only for Enterprise Edition, CT was available for all editions of SQL Server 2008. The goal of this 3-part series is to see how these two existing SQL Server features compare and contrast with SQL Server 2016 Temporal Tables.

Solution

In Part 1 of this series, we are going to look deeply into the Change Tracking feature of SQL Server. Then in part 2 we will look deeper into Change Data Capture functionality and uses.

Before we dive in, it is important to note that temporal tables are not a replacement for CDC or CT. CDC is intended to store data history for a short period of time. Depending on your application’s ETL schedule, you will move those records that are marked for capturing from the transaction log to some data warehouse fact tables. Temporal tables store the DML changes in the temporal history table and they are intended to stay there for a much longer time period.

Change Tracking (CT) is another feature that will only store the last change of the row. Although it has limited usage, some applications may only need this simple refresh functionality and it does not require temporal or CDC for data tracking purposes.

How does the SQL Server Change Tracking feature work?

1. The change tracking feature first needs to be enabled at the database level and then for each table where you want to track the changes.
2. CT keeps track of how many times a change happened in a table since it has been enabled.
3. Change tracking of the row is tracked based on the primary key column.
4. No changes to the table schema are required, but existing application code needs to be updated to take advantage of CT.

There are 2 ways in which applications and databases can synchronize data changes.

In one-way synchronization the application is responsible to keep the cache fresh with the updated changes in the table.

In two-way synchronization, changes made through the application can travel to the table and CT can track those changes in addition to the changes made at the table itself.

Then you have to use very quirky Change Tracking functions to get what changed in a table being tracked. In the following example you will see that it is a useful feature, but a little complicated and it also requires application code changes.

CREATE DATABASE TrackChange
GO

USE TrackChange
GO   

CREATE TABLE Customer ( 
CustomerId INT IDENTITY (1,1) 
,FirstName VARCHAR(30) 
,LastName VARCHAR(30) NOT NULL 
,Amount_purchased DECIMAL 
) 
GO 

ALTER TABLE dbo.Customer ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerId, LastName) 
GO 

INSERT INTO dbo.Customer ( FirstName, LastName, Amount_Purchased)
VALUES ( 'Frank', 'Sinatra',20000.00),( 'Shawn', 'McGuire',30000.00),( 'Amy', 'Carlson',40000.00)
GO

SELECT * FROM dbo.Customer

-- Now enable change Tracking at Database Level
ALTER DATABASE TrackChange
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

-- Then enable change Tracking at Table Level
ALTER TABLE dbo.Customer
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

-- Verify the status of the change tracking
-- You will find that there is no version history yet.
SELECT CHANGE_TRACKING_CURRENT_VERSION () AS CT_Version

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

SELECT c.CustomerId, c.LastName ,  ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT
FROM Customer AS c
CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct;

Sys_Change_Version values shows Null because after enabling CT, there has not been any change made to the table. Now let’s make some DML changes to this table and then again check the value of Sys_Change_Version column returned by the ChangeTable function.

-- Now make some changes in the table
-- insert a row
INSERT INTO Customer(FirstName, LastName, Amount_purchased)
VALUES('Ameena', 'Lalani', 50000)
GO

-- delete a row
DELETE FROM dbo.Customer 
WHERE CustomerId = 2
GO

-- update a row
UPDATE Customer
SET  Lastname = 'Clarkson' WHERE CustomerId = 3
GO

-- Let us query to see what it reports
SELECT CHANGE_TRACKING_CURRENT_VERSION () AS CT_Version

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

SELECT c.CustomerId, c.LastName ,  ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT
FROM Customer AS c
CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct; 

Now we see the Change Tracking Version equals to 3. We did 3 operations; 1 insert, 1 delete and 1 update. Update enters 2 rows in the change table; one delete and one insert as can be seen from the SYS_CHANGE_OPERATION column below.

Let's do 3 more changes to the Customer table.

-- Update the above row one more time
UPDATE Customer
SET  Lastname = 'Blacksmith' WHERE CustomerId = 3
GO

-- Let INSERT few more rows
INSERT INTO Customer(FirstName, LastName, Amount_purchased)
VALUES('Sponge', 'Bob', 5000)
GO

INSERT INTO Customer(FirstName, LastName, Amount_purchased)
VALUES('Donald', 'Duck', 6000)
GO

-- Let us query to see what it reports now
SELECT CHANGE_TRACKING_CURRENT_VERSION () as CT_Version

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

SELECT c.CustomerId, c.LastName ,  ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT
FROM Customer AS c
CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct;

We again did the same 3 operations and now we see the Change Tracking Version equals 6.

-- Let us make one more update
UPDATE Customer
SET  Lastname = 'Cool' WHERE CustomerId = 6
GO

SELECT CHANGE_TRACKING_CURRENT_VERSION () as CT_Version

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

SELECT c.CustomerId, c.LastName ,  ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT
FROM Customer AS c
CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct;

We observed that SYS_CHANGE_VERSION = 6 was deleted when we updated the row (customerId = 6) and the new version 7 is recorded with the same information. Hence we proved that the Change Tracking feature only stores the last change for the row, so there is no real historical data available with the Change Tracking feature. The function CHANGE_TRACKING_CURRENT_VERSION () always returns the current version of the row. Also, notice that you do not have any control on changing the behavior of the Change Tracking feature. It is very rigid and is like you either take it or leave it.

Summary

SQL Server Change Tracking is a very simple tracking tool which has limited use and there is no direct way of querying the internal tracking table that is created when CT is enabled. You can define the retention period of CT data at the database level. Only columns that are part of the primary key in a table are tracked for changes after enabling Change Tracking. In my next tip, I will discuss more about Change Data Capture (CDC).

Next Steps

• Read more about CDC and CT here.
• Learn more about CHANGETABLE function here.

About the author

Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips