The CDC (change data Capture) records the time, type, and actual data changes of the DML operation by asynchronously reading the transaction log, and then logs the data to a table that is automatically created when the CDC is enabled. Detailed data changes can be obtained through CDC-related stored procedures. Because the data change is read asynchronously, it has little effect on the overall performance, much less than the data change record implemented by trigger.
I'll explain this feature in an example below. This feature is primarily useful in ETL solutions.
Use AdventureWorksDW;
Go
EXECUTE sys.sp_cdc_enable_db--enable the database to support the CDC
go
EXEC sys.sp_cdc_enable_table ' dbo ',
' FactInternetSales ', @role_name = NULL, @supports_net_changes = 0; --enable a table to support the CDC go--
Here's supports_net_changes refers to whether or not to support the so-called net change, that is, to filter out duplicate
SELECT name, IS_TRACKED_BY_CDC From Sys.tables
WHERE name like (' fact% ');
INSERT into FactInternetSales
VALUES
(484,1127,1139,1134,18759,1,100,6, ' SO75124 ', 1,1,1,21.9800,21.9800,0,0,8.2205,8.2205,21.9800,
1.7584,0.5495,null,null);
INSERT into FactInternetSales
VALUES
(486,1127,1139,1134,18759,1,100,6, ' SO75125 ', 1,1,1,21.9800,21.9800,0,0,8.2205,8.2205,21.9800,
1.7584,0.5495,null,null);
UPDATE FactInternetSales
SET Promotionkey = 2
WHERE salesordernumber = ' SO75124 ';
DELETE from FactInternetSales WHERE salesordernumber= ' SO75125 '
SELECT * from CDC.DBO_FACTINTERNETSALES_CT; --the table is actually in the system table.