How do you read all the changes captured by CDC in a table?
USE AdventureWorks2022;
GO
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('HR_Department');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_HR_Department (@from_lsn, @to_lsn, N'all');
GO
How do you read the net changes captured by CDC in a table?
USE AdventureWorks2022;
GO
DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);
-- Obtain the beginning of the time interval.
SET @begin_time = DATEADD(day, -1, GETDATE()) ;
-- DML statements to produce changes in the HumanResources.Department table.
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES (N'MyDept', N'MyNewGroup');
UPDATE HumanResources.Department
SET GroupName = N'Resource Control'
WHERE GroupName = N'Inventory Management';
DELETE FROM HumanResources.Department
WHERE Name = N'MyDept';
-- Obtain the end of the time interval.
SET @end_time = GETDATE();
-- Map the time interval to a change data capture query range.
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SET @from_lsn = ISNULL(sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time), [sys].[fn_cdc_get_min_lsn]('HR_Department') );
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
-- Return the net changes occurring within the query window.
SELECT * FROM cdc.fn_cdc_get_net_changes_HR_Department(@from_lsn, @to_lsn, 'all');
source: cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL) – SQL Server | Microsoft Learn