Change Detection: CES, CDC, and Change Tracking

Knowing when your data changes

The big comparison

Change Tracking vs Change Data Capture vs Change Event Streaming

Feature	Change Tracking	CDC	CES
What it records	Which rows changed (row version)	Full before/after column values	Change events streamed externally
Storage impact	Minimal (version numbers only)	Moderate (change tables mirror source)	Minimal on SQL (events go to Event Hubs)
Latency	Poll-based (you query for changes)	Poll-based (read change tables)	Near real-time push
History depth	Configurable retention (default 2 days)	Configurable retention	Depends on Event Hubs retention
Old/new values	No — only which rows changed	Yes — full before and after images	Yes — change events include values
Destination	Query from same database	Query from change tables in same database	Azure Event Hubs (external consumers)
Best for	Sync scenarios (mobile offline sync, ETL delta loads)	Audit trails, data warehousing, regulatory compliance	Real-time event-driven architectures, microservices

Change Tracking

Change Tracking is the lightest option. It marks rows as changed and assigns version numbers, but does not store what the old values were.

Enable and use

-- Enable at database level
ALTER DATABASE VaultBankDB SET CHANGE_TRACKING = ON
  (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);

-- Enable on a specific table
ALTER TABLE dbo.Customers ENABLE CHANGE_TRACKING
  WITH (TRACK_COLUMNS_UPDATED = ON);

Querying changes

-- Get the current version (your sync bookmark)
DECLARE @lastSync BIGINT = CHANGE_TRACKING_CURRENT_VERSION();

-- Later: get rows changed since last sync
SELECT
    ct.CustomerId,
    ct.SYS_CHANGE_OPERATION,   -- 'I' insert, 'U' update, 'D' delete
    ct.SYS_CHANGE_VERSION,
    c.FullName,
    c.Email
FROM CHANGETABLE(CHANGES dbo.Customers, @lastSync) AS ct
LEFT JOIN dbo.Customers c ON ct.CustomerId = c.CustomerId;

The pattern: save the version number after each sync. Next time, query for everything changed since that version. This is exactly how offline-capable mobile apps synchronise data — download only what changed since the last sync.

Change Data Capture (CDC)

CDC captures the complete before and after images of every changed row. It reads from the transaction log and writes to system-generated change tables.

Enable and use

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

-- Enable on a specific table
EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'Transactions',
    @role_name = 'cdc_reader',
    @supports_net_changes = 1;

Reading changes

-- Get the LSN range for the last hour
DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn('dbo_Transactions');
DECLARE @to_lsn BINARY(10) = sys.fn_cdc_get_max_lsn();

-- All individual changes (every insert, update, delete)
SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_Transactions(
    @from_lsn, @to_lsn, 'all update old');

-- Net changes (final state of each row, collapses multiple changes)
SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_Transactions(
    @from_lsn, @to_lsn, 'all');

Key difference: all_changes returns every individual change (a row updated three times shows three rows). net_changes returns only the final state (same row shows once with the last values).

💡 Exam tip: All changes vs Net changes

This distinction appears frequently on the exam:

• fn_cdc_get_all_changes — returns every change operation. Use for audit trails where you need the complete history of modifications.
• fn_cdc_get_net_changes — collapses multiple changes to the same row into the final result. Use for ETL/data warehouse loads where you only need the current state.

If a question says “load the latest version of changed rows into a data warehouse,” the answer is net changes. If it says “record every modification for compliance,” the answer is all changes.

Change Event Streaming (CES)

CES is the newest option, introduced with SQL Server 2025. Instead of polling for changes, CES pushes change events to Azure Event Hubs in near real-time.

Why CES matters

With Change Tracking and CDC, your consuming application must poll — repeatedly asking “did anything change?” CES flips this model. Changes are streamed out as events the moment they happen. Downstream systems (microservices, analytics pipelines, notification systems) subscribe to the event stream.

Setup

-- Create an event streaming group
EXEC sys.sp_change_event_streaming_create_group
    @group_name = 'TransactionEvents',
    @event_hub_namespace = 'vaultbank-events.servicebus.windows.net',
    @event_hub_name = 'transactions',
    @credential_name = 'EventHubCredential';

-- Add a table to the streaming group
EXEC sys.sp_change_event_streaming_add_table
    @group_name = 'TransactionEvents',
    @schema_name = 'dbo',
    @table_name = 'Transactions';

Events arrive at Event Hubs in near real-time. From there, Azure Stream Analytics, Azure Functions, or any Event Hubs consumer can process them.

CES vs CDC

Aspect	CDC	CES
Consumer pulls or system pushes?	Consumer polls change tables	System pushes to Event Hubs
Where is change data stored?	In the SQL database (change tables)	In Azure Event Hubs (external)
Database storage impact	Change tables grow with volume	Minimal — events leave the database
Real-time capability	Near real-time with frequent polling	True near real-time streaming
Best consumer type	SQL queries, ETL tools	Event-driven services, stream processors

💡 Scenario: Dev builds real-time fraud detection at PixelForge

PixelForge Studios processes in-game purchase transactions. Dev Kapoor needs to detect suspicious purchasing patterns in real-time — not minutes later.

CDC would work but requires polling the change tables every few seconds, adding load to the database. Instead, Dev enables CES on the Transactions table, streaming events to Azure Event Hubs. An Azure Stream Analytics job watches the stream for patterns (multiple high-value purchases from the same account within seconds). Suspicious patterns trigger an Azure Function that flags the account.

The key insight: CES removes the polling loop. The database pushes events outward instead of consumers pulling inward.

Azure Functions SQL trigger binding

Azure Functions can react to SQL changes without you writing polling logic. The SQL trigger binding uses Change Tracking under the hood.

// Azure Function triggered by changes to the Orders table
[FunctionName("OrderChanged")]
public static void Run(
    [SqlTrigger("[dbo].[Orders]",
        ConnectionStringSetting = "SqlConnectionString")]
    IReadOnlyList<SqlChange<Order>> changes,
    ILogger log)
{
    foreach (var change in changes)
    {
        log.LogInformation($"Order {change.Item.OrderId} was {change.Operation}");

        if (change.Operation == SqlChangeOperation.Insert)
        {
            // Send order confirmation email
            SendConfirmation(change.Item);
        }
    }
}

Key facts:

• Requires Change Tracking enabled on the table (the binding manages this automatically)
• Tracks changes using a leases table in the database
• Supports Insert, Update, and Delete operations
• Scales with Azure Functions consumption plan (pay per execution)

Azure Logic Apps

Logic Apps provide a no-code/low-code approach to reacting to data changes. They use the SQL connector with triggers like “When an item is modified”:

Trigger: When a row is modified in dbo.Orders
  → Condition: Order.Status equals 'Shipped'
    → Yes: Send email notification via Office 365
    → Yes: Update tracking record in Cosmos DB
    → No: Do nothing

Logic Apps are best when the reaction involves multiple services (email, Teams, external APIs) and the team prefers visual workflow design over code.

Choosing the right mechanism

Choosing the right change detection mechanism

Scenario	Best Choice	Why
Mobile app offline sync	Change Tracking	Lightweight, only needs to know which rows changed since last sync
Data warehouse incremental load	CDC (net changes)	Needs current state of changed rows, does not need real-time
Regulatory audit trail	CDC (all changes)	Must record every modification with before/after values
Real-time event-driven microservices	CES	Push-based, near real-time, decouples consumers from database
Send email when order ships	Azure Functions SQL trigger or Logic Apps	Serverless reaction, no infrastructure to manage
Complex multi-service workflow on change	Logic Apps	Visual designer, built-in connectors for hundreds of services

Question

What is the key difference between Change Tracking and CDC?

Click or press Enter to reveal answer

Answer

Change Tracking records WHICH rows changed (version numbers only — lightweight). CDC records the complete BEFORE and AFTER values of every changed column (detailed but heavier on storage). Use Change Tracking for sync scenarios; use CDC for audit trails and data warehousing.

Click to flip back

Question

What is Change Event Streaming (CES) and how is it different from CDC?

Click or press Enter to reveal answer

Answer

CES pushes change events to Azure Event Hubs in near real-time. Unlike CDC, which stores changes inside the database for consumers to poll, CES sends events externally the moment they happen. This reduces database load and enables true event-driven architectures without polling loops.

Click to flip back

Question

What does the Azure Functions SQL trigger binding use under the hood?

Click or press Enter to reveal answer

Answer

The SQL trigger binding uses Change Tracking internally. It enables Change Tracking on the target table (if not already enabled) and maintains a leases table to track which changes have been processed. You do not need to manage the polling or version tracking — the binding handles it.

Click to flip back

Question

When should you use fn_cdc_get_net_changes instead of fn_cdc_get_all_changes?

Click or press Enter to reveal answer

Answer

Use net_changes when you only need the FINAL state of each changed row — typical for ETL loads into a data warehouse. Use all_changes when you need the complete modification history — required for audit compliance where every individual change must be recorded.

Click to flip back

Knowledge Check

Priya at Vault Bank needs to load changed transaction data into a data warehouse every night. She only needs the latest version of each changed row, not every intermediate change. The data warehouse loads run as a scheduled batch job. Which mechanism should she use?

AChange TrackingBCDC with fn_cdc_get_net_changesCChange Event Streaming (CES)DAzure Functions SQL trigger

Check Answer

Knowledge Check

Dev at PixelForge needs to detect fraudulent in-game purchases within seconds of them occurring. The fraud detection service runs as a separate microservice outside the SQL database. Which approach is best?

ACDC with frequent polling every 2 secondsBChange Tracking queried by the microserviceCChange Event Streaming (CES) to Azure Event Hubs, consumed by the fraud detection microserviceDA database trigger that calls the microservice directly via HTTP

Check Answer

🎬 Video coming soon

Next up: Azure Integration and Monitoring — monitor your SQL databases, DAB endpoints, and pipelines with Azure Monitor, Application Insights, and Log Analytics.