CDC change data capture

CDC change data capture(2013-03-20 15:25:52)

Category: SQL

four ways to record data changes in SQL Server: Trigger, OUTPUT clause, change data capture feature, synchronous change tracking.

The CDC is recorded here: change data Capture records INSERT, UPDATE, and delete activities that are applied to SQL Server tables. Enable change data capture for a table in the database, example: CREATE DATABASE test1 Use test1CREATE TABLE [dbo]. [WSTTESTTBL] ([id] [int] IDENTITY (*) not NULL,[name] [nvarchar] (+) NULL) on [PRIMARY] sp_cdc_enable_db--Enable change data capture for the current databaseSELECT is_cdc_enabled from sys.databases WHERE name= ' test1 '--enable change data capture for database success sp_cdc_enable_table @source_schema = ' dbo ', @source_name = ' wsttesttbl ', @role_name = ' cdc_role '-the table identified by the source table as a tracked ( Condition: Start SQLServerAgent service)  --execute sys.sp_cdc_help_change_data_capture--Change data capture configuration information for a specified table--@source_schema = N ' dbo ',--@source_name = N ' wsttesttbl ';--go INSERT into WSTTESTTBL values (' Jiayiw ')Delete from wsttesttbl WHERE name = ' Jiayiw 'INSERT into WSTTESTTBL values (' wangshuting ')UPDATE wsttesttbl SET name= ' Zhangjie ' WHERE name= ' wangshuting '--select * from WsttesttblSELECT * from Cdc.dbo_wsttesttbl_ct --__$operation=2, indicates a new--__$operation=3 or 4, indicating an update, 3 representing the old value, and 4 representing the new value--__$operation=1 of the situation, indicating the deletion 

In addition, you can use CDC to implement incremental loading (SSIS)

CDC change data capture