I have Change Data Capture enabled and working fine. The question that I have is how do I query the data to show users what changes occurred.
You use built-in table-valued functions (TVFs) as described in Books Online : Using Change Data (MSDN).
First you need to find the LSNs you want to use to filter the ranges you want to query. These LSNs will represent points in the log which you can correlate to datetime values. You can do this using sys.fn_cdc_map_time_to_lsn. Let's say you want to report on all the change data captured today:
DECLARE @s DATE = SYSDATETIME(), -- start today at midnight @e DATE = DATEADD(DAY, 1, SYSDATETIME()), -- end tomorrow at midnight @sl BINARY(10), -- start LSN @el BINARY(10); -- end LSN SELECT @sl = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @s), @el = sys.fn_cdc_map_time_to_lsn('largest less than', @e); SELECT @sl, @el; -- start LSN and end LSN within today
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_floob(@sl, @el, 'all'); SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_floob(@sl, @el, 'all');