Understanding Change Tracking in SQL Server

1. What Is Change Tracking?

Change Tracking is a built-in, lightweight feature of SQL Server that enables applications to efficiently detect which rows have changed in a table since a given version—without capturing full historical data

• It automatically logs inserts, updates, and deletes in internal side tables.
• Developers can query it using functions instead of implementing custom triggers or audit tables.
• Two key questions it answers:
  1. Which rows have changed? (Not how many times or what the old values were)
  2. Has a specific row changed?

---

2. Key Differences: CDC vs. Change Tracking

Feature	Change Tracking (CT)	Change Data Capture (CDC)
Change history retention	Only the latest change per row	Full history, with before/after images
Data captured	“Row changed” flag, optionally columns changed	Full column data of each change
Overhead	Low, synchronous, minimal impact on DML	Higher, asynchronous via transaction log
Use case	Simple sync, cache refresh, conflict detection	Auditing, ETL, historical data analysis

Change Tracking is best when you need to know what rows changed and quickly fetch current data, while CDC is more suitable for detailed historical tracking.

---

3. When to Use Each

When to use Change Tracking:

• One-way or two-way synchronization between SQL Server and other data stores.
• Cache invalidation or lightweight client sync.
• Scenarios where only the current state and “whether changed” matters (Microsoft Learn).

When to use Change Data Capture:

• Auditing full data history with before/after values.
• ETL pipelines needing detailed delta data.
• Replicating all changes to data warehouses or analytical stores .

---

4. How to Enable Change Tracking (Step-by-Step)

Here’s a basic example showing how to enable Change Tracking from database to table level:

-- 1. Create database
CREATE DATABASE CTDB;
GO
USE CTDB;
GO

-- 2. Enable Change Tracking on database
ALTER DATABASE CTDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO

-- 3. Create a sample table
CREATE TABLE dbo.Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  OrderAmount DECIMAL(10,2)
);
GO

-- 4. Enable Change Tracking on the table
ALTER TABLE dbo.Orders
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);  -- Optional: track which columns changed
GO

5. Example: Viewing Changes After Modifying a Table

Now that Change Tracking is enabled on the dbo.Orders table, let’s walk through a sample session where we insert, update, and delete some data—and then retrieve the changes using CHANGETABLE(...).

🔧 Step 1: Insert, Update, and Delete Some Rows

-- INSERT a new order
INSERT INTO dbo.Orders (OrderID, CustomerID, OrderAmount)
VALUES (1, 101, 250.00);

-- UPDATE the existing order
UPDATE dbo.Orders
SET OrderAmount = 275.00
WHERE OrderID = 1;

-- DELETE the order
DELETE FROM dbo.Orders
WHERE OrderID = 1;

---

🔎 Step 2: Query the Change Table

Before executing the operations, assume we saved the current version using:

DECLARE @last_sync_version BIGINT;
SET @last_sync_version = CHANGE_TRACKING_CURRENT_VERSION();

Then, after making changes, we can retrieve the changes since that version:

SELECT CT.*
FROM CHANGETABLE(CHANGES dbo.Orders, @last_sync_version) AS CT;

To summarize, you can retrieve information from the change table by executing the following query:

-- Step 1: Save current version before changes
DECLARE @version BIGINT = CHANGE_TRACKING_CURRENT_VERSION();

-- Step 2: Perform some changes
INSERT INTO dbo.Orders (OrderID, CustomerID, OrderAmount)
VALUES (100, 200, 300.00);

-- Step 3: Get changes with alias
SELECT *
FROM CHANGETABLE(CHANGES dbo.Orders, @version) AS CT;

Explanation:

• SYS_CHANGE_OPERATION:
  • I = Insert
  • U = Update
  • D = Delete
• SYS_CHANGE_VERSION shows the internal version number for each change.
• SYS_CHANGE_COLUMNS (if TRACK_COLUMNS_UPDATED = ON) shows which columns were modified (as a bit mask).