Change Data Capture (CDC)
7 questions found
What is Change Data Capture in SQL Server, and what problem does it solve?
Beginner
Change Data Capture, often called CDC, automatically records insert, update, and delete activity on a table into a separate set of change tables, letting other systems or processes easily identify exactly what data has changed since the last time they checked, without needing to compare entire tables manually.
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'Orders',
@role_name = NULL;
Real-world example
A data warehouse team enables CDC on their orders table so their nightly synchronization process only needs to pull the handful of orders that actually changed that day, instead of reprocessing the entire orders table every single night.
Common follow-ups: What is the difference between CDC and Change Tracking?;Does enabling CDC require any changes to the application code that writes to the table?
Replication;Query Optimization & Plans
How do you enable Change Data Capture on a specific table, and what does that process actually create?
Intermediate
You first enable CDC at the database level using sp_cdc_enable_db, and then enable it for a specific table using sp_cdc_enable_table, which creates a corresponding change table that automatically captures every insert, update, and delete made to the original table going forward, along with the specific columns that changed.
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Customers',
@role_name = NULL,
@supports_net_changes = 1;
Real-world example
A company enables CDC on their customers table, allowing their integration team to query a dedicated change table to see every customer record that was added, updated, or removed since their last data sync.
Common follow-ups: What does the supports_net_changes option actually control?;How much extra storage does enabling CDC typically require?
Data Types & Schema Design;SQL Server Architecture & Editions
How do you query the change data captured for a table to find out exactly what changed within a specific time range?
Intermediate
You use the cdc.fn_cdc_get_all_changes or cdc.fn_cdc_get_net_changes table valued functions, passing in the starting and ending log sequence numbers that correspond to your desired time range, which returns every captured change along with an indicator showing whether each row was inserted, updated, or deleted.
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_all_changes_dbo_Orders(@from_lsn, @to_lsn, 'all');
Real-world example
An integration process pulls all order changes from the past hour using CDC's change functions, feeding only the actual modified records into a downstream reporting system instead of reprocessing the whole orders table.
Common follow-ups: What do the different operation codes in the CDC result set actually mean?;How do you convert a specific date and time into the log sequence number CDC functions require?
Query Optimization & Plans;Data Types & Schema Design
How would you use Change Data Capture to build a near real time data synchronization pipeline between an operational database and a data warehouse?
Advanced
You would schedule a job that periodically queries the CDC change functions for each tracked table since the last successful run, apply those specific inserted, updated, and deleted rows to your data warehouse tables, and keep track of the last processed log sequence number so each run only picks up genuinely new changes.
-- Store the last processed LSN and use it as the starting point
-- for the next scheduled synchronization run
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Orders(@last_lsn, @current_lsn, 'all');
Real-world example
A retail analytics team builds a synchronization job that runs every ten minutes, pulling only the changed orders since the last run using CDC, keeping their data warehouse nearly real time without the heavy load of reprocessing the entire orders table repeatedly.
Common follow-ups: How do you handle a situation where the synchronization job fails partway through processing changes?;What is the retention period for CDC change data, and how do you configure it?
Replication;SQL Server Agent & Job Scheduling
What are the performance and storage considerations to keep in mind before enabling Change Data Capture on a very high volume table?
Advanced
CDC adds some overhead to every insert, update, and delete since it must also write to the change table, and the change tables themselves consume additional storage that grows continuously until old changes are cleaned up, so you should monitor the cleanup job's schedule and retention period carefully on very high volume tables.
EXEC sys.sp_cdc_change_job
@job_type = 'cleanup',
@retention = 4320; -- retention in minutes
Real-world example
A high traffic order processing system carefully tunes its CDC cleanup job retention period after noticing the change tables were growing faster than expected, balancing how long change history is kept against available storage.
Common follow-ups: How do you monitor how much storage CDC change tables are actually consuming?;What happens if the CDC cleanup job falls behind on a very busy system?
Query Optimization & Plans;Backup & Recovery
How is Change Data Capture different from using triggers to track changes to a table?
Intermediate
CDC captures changes asynchronously by reading the transaction log in the background, adding minimal overhead to the original insert, update, or delete operation, while triggers run synchronously as part of the same transaction that made the change, which can add noticeable overhead and complexity directly to your application's write operations.
-- CDC reads the transaction log asynchronously
-- Triggers execute immediately within the same transaction
CREATE TRIGGER trg_AuditOrders ON Orders
AFTER INSERT, UPDATE, DELETE
AS BEGIN
-- custom logic runs synchronously here
END;
Real-world example
A team switches from a custom trigger based change tracking solution to CDC after noticing their triggers were slowing down every single order update, since CDC captures the same information with far less impact on write performance.
Common follow-ups: In what situations would a trigger still be a better choice than CDC?;Does CDC require Enterprise edition or is it available in Standard edition?
Triggers;SQL Server Architecture & Editions
How do you disable Change Data Capture on a table or an entire database once it is no longer needed?
Intermediate
You use the sp_cdc_disable_table stored procedure to remove CDC tracking from a specific table, which also drops its associated change table, and use sp_cdc_disable_db to completely remove CDC support from the entire database once no tables are being tracked anymore.
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'Orders',
@capture_instance = N'dbo_Orders';
Real-world example
A team disables CDC on an old table that is no longer part of their synchronization process, freeing up storage that was being used by its change table and reducing unnecessary overhead on that table's writes.
Common follow-ups: What happens to the historical change data once CDC is disabled?;Do you need to stop any related jobs before disabling CDC?
SQL Server Architecture & Editions;Backup & Recovery