Enables change data capture for the specified source table in the current database. When a table is enabled for change data capture, a record of each data manipulation language (DML) operation applied to the table is written to the transaction log. The change data capture process retrieves this information from the log and writes it to change tables that are accessed by using a set of functions.
Change data capture is available only in SQL Server 2008 Enterprise, Developer, and Evaluation editions.
sys.sp_cdc_enable_table [ @source_schema = ] 'source_schema', [ @source_name = ] 'source_name' , [ @role_name = ] 'role_name' [,[ @capture_instance = ] 'capture_instance' ] [,[ @supports_net_changes = ] supports_net_changes ] [,[ @index_name = ] 'index_name' ] [,[ @captured_column_list = ] 'captured_column_list' ] [,[ @filegroup_name = ] 'filegroup_name' ] [,[ @allow_partition_switch = ] 'allow_partition_switch' ]
Before you can enable a table for change data capture, the database must be enabled. To determine whether the database is enabled for change data capture, query the is_cdc_enabled column in the sys.databases catalog view. To enable the database, use the sys.sp_cdc_enable_db stored procedure.
When change data capture is enabled for a table, a change table and one or two query functions are generated. The change table serves as a repository for the source table changes extracted from the transaction log by the capture process. The query functions are used to extract data from the change table. The names of these functions are derived from the capture_instance parameter in the following ways:
All changes function: cdc.fn_cdc_get_all_changes_<capture_instance>
Net changes function: cdc.fn_cdc_get_net_changes_<capture_instance>
sys.sp_cdc_enable_table also creates the capture and cleanup jobs for the database if the source table is the first table in the database to be enabled for change data capture and no transactional publications exist for the database. It sets the is_tracked_by_cdc column in the sys.tables catalog view to 1.
SQL Server Agent does not have to be running when change data capture is enabled for a table. However, the capture process will not process the transaction log and write entries to the change table unless SQL Server Agent is running.
A. Enabling change data capture by specifying only required parameters
The following example enables change data capture for the HumanResources.Employee table. Only the required parameters are specified.
USE AdventureWorks2008R2; GO EXECUTE sys.sp_cdc_enable_table @source_schema = N'HumanResources' , @source_name = N'Employee' , @role_name = N'cdc_Admin'; GO
B. Enabling change data capture by specifying additional optional parameters
The following example enables change data capture for the HumanResources.Department table. All parameters except @allow_partition_switch are specified.
USE AdventureWorks2008R2; GO EXEC sys.sp_cdc_enable_table @source_schema = N'HumanResources' , @source_name = N'Department' , @role_name = N'cdc_admin' , @capture_instance = N'HR_Department' , @supports_net_changes = 1 , @index_name = N'AK_Department_Name' , @captured_column_list = N'DepartmentID, Name, GroupName' , @filegroup_name = N'PRIMARY'; GO