Temporal Tables vs Change Data Capture: Which One Should You Use?

Every database developer or DBA, at some point in their career, has needed an answer to questions like: “what did this row look like last Tuesday?” or “what changed in this table in the last hour?”. SQL Server gives us two very different built-in ways to do it: Temporal Tables and Change Data Capture (CDC).

They sound similar on the surface as both track changes to data over time but they solve different problems and work in completely different ways. This article breaks down what each one actually does, walks through real examples, and gives you a clear way to decide which one fits your situation.

The One-Line Difference

• Temporal Tables answer: “What did this data look like at any point in time?”
• Change Data Capture answers: “What exactly changed, so I can move it somewhere else?”

Temporal Tables are about history and point-in-time queries. CDC is about feeding changes downstream, to a data warehouse, an ETL pipeline, or another system. Keep that distinction in mind; it explains almost every design decision below.

Part 1: Temporal Tables

What They Are

A temporal table, officially called a “system-versioned temporal table,” was introduced in SQL Server 2016. It is a regular table that SQL Server automatically pairs with a separate history table, which SQL Server maintains alongside the current table. Every time a row is updated or deleted, SQL Server automatically copies the old version of that row into the history table complete with the system-maintained time period during which that version was valid. You don’t write any code to make this happen; it’s built into the table itself.

Setting One Up

CREATE TABLE dbo.Employee
(
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(50),
    Salary DECIMAL(10,2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

That’s it. SQL Server now silently maintains dbo.Employee_History for you, with no triggers and no extra code.

Seeing It in Action

-- Day 1: Insert a new employee
INSERT INTO dbo.Employee (EmployeeID, Name, Department, Salary)
VALUES (101, 'Priya Sharma', 'Engineering', 95000);

-- A few months later: she gets a raise and moves teams
UPDATE dbo.Employee
SET Salary = 108000, Department = 'Platform Engineering'
WHERE EmployeeID = 101;

At this point, the current table shows only the latest row. But the history table already holds the old version automatically. Now you can ask time-travel questions:

-- What did Priya's record look like on a specific date?
SELECT *
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2026-03-15'
WHERE EmployeeID = 101;

-- Show me every version of her record, ever
SELECT *
FROM dbo.Employee
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 101
ORDER BY ValidFrom;

The second query returns both the original row (Engineering, 95000) and the current one (Platform Engineering, 108000), each tagged with exactly when it was valid.

Temporal system-time values are based on UTC. When using AS OF with a local business time, convert that time appropriately before querying.

What Temporal Tables Are Great For

• Auditing: “show me exactly what this customer’s address was when the order was placed”
• Compliance and regulatory reporting that requires historical accuracy
• Undoing accidental bad updates. You can literally query the row as it looked five minutes ago
• Trend analysis on slowly changing data (salary history, price history, status history)

What Temporal Tables Are Not Good For

• Streaming changes to another system in near real time
• Capturing every intermediate state within a single transaction: Temporal tables record row versions based on transaction boundaries. If the same row is modified multiple times within one transaction, SQL Server can generate zero-duration history rows; normal FOR SYSTEM_TIME queries filter those rows out. If you need to analyze those intermediate versions, you need to query the history table directly.
• On high-churn tables, the history table can grow rapidly, so storage, indexing, and history-retention strategy need to be planned carefully.

Part 2: Change Data Capture (CDC)

What It Is

CDC, introduced in SQL Server 2008, works completely differently. Instead of maintaining a history table by copying full row versions, it reads the transaction log in the background and records exactly which rows were inserted, updated, or deleted along with the actual column-level values into dedicated change tables. It’s designed from the ground up to feed that change data to another process, like an ETL job or a data warehouse load.

Setting It Up

-- Enable CDC at the database level first
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on a specific table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Orders',
    @role_name     = NULL,
    @supports_net_changes = 1;

This creates a change table behind the scenes, typically named something like cdc.dbo_Orders_CT, along with SQL Server Agent jobs that continuously read the transaction log and populate it.

Seeing It in Action

-- Someone updates an order
UPDATE dbo.Orders
SET Status = 'Shipped', ShippedDate = '2026-08-20'
WHERE OrderID = 5001;

A few seconds later (CDC runs asynchronously via the log reader), you can query exactly what changed:

SELECT
    __$operation,   -- 1 = delete, 2 = insert, 3 = update (before), 4 = update (after)
    OrderID,
    Status,
    ShippedDate,
    __$start_lsn    -- log sequence number, tells you the order of changes
FROM cdc.dbo_Orders_CT
WHERE OrderID = 5001
ORDER BY __$start_lsn;

For an update, CDC can therefore provide both the before-image and after-image, depending on how the changes are queried.

This returns individual change events you can see the “before” and “after” versions of the update as two separate rows, tagged with an operation type. This event-based format is exactly what a downstream ETL tool wants: “give me every change since the last time I checked, in order.”

There’s also a convenient net-changes function for pulling just the final state of what changed in a window:

DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn('dbo_Orders');
DECLARE @to_lsn BINARY(10) = sys.fn_cdc_get_max_lsn();

SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_Orders(@from_lsn, @to_lsn, 'all');

What CDC Is Great For

• Incremental ETL: only pulling rows that actually changed instead of reloading a whole table every night
• Feeding a data warehouse or data lake with near real-time change events
• Integrating with message queues or streaming platforms downstream
• CDC captures data changes, but it should not automatically be treated as a complete application audit trail. For example, requirements such as “which application user made the change and why?” may require additional auditing mechanisms.

What CDC Is Not Good For

• Simple “what did this record look like on this date” business queries. you’d have to reconstruct that yourself from the change events, which is more work than a Temporal Table’s built-in FOR SYSTEM_TIME AS OF
• Long-term historical retention out of the box, CDC change tables are meant to be consumed and cleaned up on a schedule (default retention is 3 days), not kept forever. Please note: The default CDC retention period is typically three days, but retention can be configured.
• Environments without SQL Server Agent running, since On SQL Server and Azure SQL Managed Instance, CDC capture and cleanup use SQL Server Agent jobs. Azure SQL Database uses an internal scheduler instead.

Side-by-Side Comparison

Aspect	Temporal Tables	Change Data Capture
Purpose	Point-in-time history queries	Feeding changes to downstream systems
How it works	Copies full row versions to a history table	Reads the transaction log for change events
Query style	FOR SYSTEM_TIME AS OF / ALL / BETWEEN	Query change tables or use CDC functions
Granularity	Row state between transactions	Individual insert/update/delete events
Retention	Kept indefinitely (you manage cleanup)	Short-term by default (e.g., 3 days), meant to be consumed
Requires SQL Server Agent	No	Yes
Typical consumer	Application code, auditors, analysts running direct SQL	ETL pipelines, data warehouses, integration tools
Storage growth	Can grow large on high-churn tables	Smaller, since it’s typically cleaned up regularly
Availability	All SQL Server editions since 2016	SQL Server Standard and Enterprise; Developer supports the feature set for development/testing. Availability varies by SQL Server version and deployment type.

Can You Use Both Together?

Yes and it’s actually a common pattern. A table like Orders might have:

• Temporal Tables enabled, so support staff and auditors can ask “what did this order look like before the customer complained about the wrong shipping address”
• CDC enabled, so a nightly (or near real-time) pipeline picks up exactly which orders changed and pushes them into a reporting warehouse

They don’t conflict with each other. They’re solving different problems on the same table, using different underlying mechanisms.

A Simple Way to Decide

Ask yourself: “Who is going to consume this history, and how?”

• If the answer is “a person, running a query, asking about a specific point in time” → use Temporal Tables
• If the answer is “another system, that needs a stream of every change as it happens” → use CDC
• If the answer is “both” → enable both, they’re independent features

Final Thought

Temporal Tables and CDC often get lumped together because both involve “tracking changes,” but they were built for genuinely different jobs. Temporal Tables give you a built-in time machine for your data with almost zero setup effort. CDC gives you a reliable, ordered feed of exactly what changed, built for moving data between systems. Once you know which question you’re actually trying to answer, “what did this look like” versus “what changed”, the right choice becomes obvious.