How to use Change Data Capture in SQL Server — step by step

Change Data Capture in SQL Server allows you to record changes to a table (insert/update/delete) efficiently, useful for incremental loads and auditing. This guide shows how to enable CDC, read changes with fn_cdc functions and manage retention and disablement.

Prerequisites

• SQL Server 2016 SP1 or later (CDC available in this and later versions).
• sysadmin or db_owner permission on the target database.
• SQL Server Agent running (CDC uses Agent jobs).
• A table with a primary key (example uses dbo.Products).

Step 1: Enable CDC on the database

First, enable CDC at the database level. This creates the control tables and allows capture instances to be recorded.

USE YourDatabaseName;
GO
EXEC sys.sp_cdc_enable_db;
GO

Step 2: Create sample table (if needed) and enable CDC on the table

The table needs a primary key. If you already have a table, skip to enabling. Then register the table for capture.

-- Example table creation
CREATE TABLE dbo.Products (
  ProductID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(100),
  Price DECIMAL(10,2)
);
GO

-- Enable CDC on the table
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'Products',
  @role_name     = NULL; -- NULL allows read access to any user with permissions
GO

Step 3: Generate changes on the table (insert / update / delete)

Insert some rows and perform updates/deletes so there are changes to be captured.

INSERT INTO dbo.Products (Name, Price) VALUES ('Caneta', 1.20);
INSERT INTO dbo.Products (Name, Price) VALUES ('Caderno', 3.45);

UPDATE dbo.Products SET Price = 1.50 WHERE Name = 'Caneta';
DELETE FROM dbo.Products WHERE Name = 'Caderno';
GO

Step 4: Query changes with fn_cdc_get_all_changes

To read changes use the CDC system functions. Get the minimum and maximum LSNs and query cdc.fn_cdc_get_all_changes_. The default capture_instance is schema_tablename (e.g.: dbo_Products).

DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_Products');
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

SELECT 
  __$start_lsn,
  __$seqval,
  CASE __$operation
    WHEN 1 THEN 'delete'
    WHEN 2 THEN 'insert'
    WHEN 3 THEN 'update_before'
    WHEN 4 THEN 'update_after'
  END AS operation,
  ProductID, Name, Price
FROM cdc.fn_cdc_get_all_changes_dbo_Products(@from_lsn, @to_lsn, 'all');
GO

Quick interpretation: __$operation = 1 (delete), 2 (insert), 3 (update before), 4 (update after). Use the _before_ and _after_ rows to detect value changes.

Step 5: Manage retention and CDC jobs

CDC creates SQL Server Agent jobs for capture and cleanup. The default retention is 3 days (in minutes: 4320). You can adjust the cleanup job with sp_cdc_change_job.

-- Change retention to 7 days (10080 minutes)
EXEC sys.sp_cdc_change_job
  @job_type = N'cleanup',
  @retention = 10080;
GO

Check related jobs with sp_help_job or in SQL Server Agent. To disable capture on a table or on the database use:

-- Disable CDC on the table
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'Products', @capture_instance = N'dbo_Products';
GO

-- Disable CDC on the database
EXEC sys.sp_cdc_disable_db;
GO

Verify the result

Confirm that the change tables exist and that there are records:

-- View active change tables in the database
SELECT * FROM cdc.change_tables;

-- Read captured data (example already given in Step 4)
-- If you get rows with __$operation you can verify the performed changes.

If no changes appear check: SQL Server Agent is running, permissions (sysadmin/db_owner), and that the table has a primary key. Common error: "The specified table does not have a primary key" — add a PK to the table before enabling CDC.

Conclusion

After enabling and testing Change Data Capture in SQL Server, you can use the data for incremental loads in ETL, auditing or custom replication. Suggested next steps: integrate CDC reads into an SSIS/ADF process, or use the fn_cdc_get_net_changes function to obtain only the final state. Tip: review and adjust retention according to the change volume to avoid excessive growth of the change tables — want to try with a larger test volume?