sys.sp_cdc_enable_table (Transact-SQL)

适用于:SQL Server (所有受支持的版本)

为当前数据库中指定的源表启用变更数据捕获。 对表启用变更数据捕获时,应用于此表的每个数据操纵语言 (DML) 操作的记录都将写入事务日志中。 变更数据捕获进程将从日志中检索此信息,并将其写入可通过使用一组函数访问的更改表中。

更改数据捕获在 Microsoft SQL Server的每个版本中都不可用。 有关 SQL Server各版本支持的功能列表,请参阅 SQL Server 2016 各个版本支持的功能

主题链接图标Transact-SQL 语法约定

语法

  
sys.sp_cdc_enable_table   
  [ @source_schema = ] 'source_schema',   
  [ @source_name = ] 'source_name' ,  [,[ @capture_instance = ] 'capture_instance' ]  
  [,[ @supports_net_changes = ] supports_net_changes ]  
  , [ @role_name = ] 'role_name'  
  [,[ @index_name = ] 'index_name' ]  
  [,[ @captured_column_list = ] 'captured_column_list' ]  
  [,[ @filegroup_name = ] 'filegroup_name' ]  
  [,[ @allow_partition_switch = ] 'allow_partition_switch' ]  
  [;]  

参数

[ @source_schema = ] 'source_schema' 源表所属的架构的名称。 source_schemasysname,无默认值,不能为 NULL。

[ @source_name = ] 'source_name' 启用更改数据捕获的源表的名称。 source_namesysname,无默认值,不能为 NULL。

当前 数据库中必须存在source_name。 无法为更改数据捕获启用 cdc 架构中的表。

[ @role_name = ] 'role_name' 用于入口访问更改数据的数据库角色的名称。 role_namesysname ,必须指定。 如果显式设置为 NULL,则没有控制角色用于限制对更改数据的访问。

如果当前存在该角色,则使用它。 如果不存在该角色,则会尝试创建具有指定名称的数据库角色。 在尝试创建该角色之前,将删除角色名称字符串右侧的空格。 如果调用方无权在数据库中创建角色,则存储过程操作将失败。

[ @capture_instance = ] 'capture_instance' 用于命名特定于实例的更改数据捕获对象的捕获实例的名称。 capture_instancesysname ,不能为 NULL。

如果未指定,则名称派生自源架构名称以及采用 schemaname_sourcename格式的源表名称。 capture_instance 不能超过 100 个字符,并且必须在数据库中唯一。 无论指定还是派生, capture_instance 都剪裁字符串右侧的任何空白。

源表最多可以有两个捕获实例。 有关详细信息,请参阅 sys.sp_cdc_help_change_data_capture (Transact-SQL)

[ @supports_net_changes = ] supports_net_changes 指示是否为此捕获实例启用对查询净更改的支持。 如果表具有主键或表具有使用参数标识@index_name的唯一索引,则supports_net_changes为默认值为 1。 否则,该参数默认为 0。

如果为 0,则只生成查询所有更改的支持函数。

如果为 1,则还会生成查询净更改所需的函数。

如果 supports_net_changes 设置为 1,则必须指定 index_name ,或者源表必须具有定义的主键。

[ @index_name = ] 'index_name_' 用于唯一标识源表中的行的唯一索引的名称。 index_namesysname ,可为 NULL。 如果指定, index_name 必须是源表的有效唯一索引。 如果指定 了index_name ,则标识索引列优先于任何定义的主键列作为表的唯一行标识符。

[ @captured_column_list = ] 'captured_column_list' 标识要包含在更改表中的源表列。 captured_column_list是 nvarchar (最大) ,可为 NULL。 如果为 NULL,则所有列都将包括在更改表中。

列名称必须是源表中的有效列。 主键索引中定义的列或由 index_name 引用的索引中定义的列必须包括在内。

captured_column_list 是列名称的逗号分隔列表。 可以选择将列表中的单个列名称放在双引号 ("") 或方括号 ([]) 中。 如果列名称包含嵌入的逗号,则必须将该列名称引起来。

captured_column_list不能包含以下保留列名称:__$start_lsn、__$end_lsn__$seqval__$operation__$update_mask

[ @filegroup_name = ] 'filegroup_name' 要用于为捕获实例创建的更改表的文件组。 filegroup_namesysname ,可为 NULL。 如果指定,则必须为当前数据库定义 filegroup_name 。 如果为 NULL,则使用默认文件组。

我们建议为变更数据捕获的更改表创建一个单独的文件组。

[ @allow_partition_switch = ] 'allow_partition_switch' 指示是否可以对启用了更改数据捕获的表执行 ALTER TABLE 的 SWITCH PARTITION 命令。 allow_partition_switch,默认值为 1。

对于非分区表,此开关设置始终为 1,并忽略实际的设置。 如果对于非分区表将此开关显式设置为 0,则发出警告 22857 以指示已忽略此开关设置。 如果对于分区表将此开关显式设置为 0,则发出警告 22356 以指示将禁止源表上的分区切换操作。 最后,如果此开关设置显式设置为 1 或允许默认为 1 并且被启用的表已分区,则发出警告 22855 以指示不会阻塞分区切换。 如果发生任何分区切换,则变更数据捕获不会跟踪由此切换产生的更改。 这将导致使用更改数据时数据不一致。

重要

SWITCH PARTITION 为元数据操作,但能导致数据更改。 与此操作相关联的数据更改不会在变更数据捕获的更改表中捕获。 请考虑一个有三个分区的表,并对此表进行更改。 捕获进程将跟踪用户对该表执行的插入、更新和删除操作。 但是,如果分区切出到另一个表(例如,为了执行大容量删除操作),作为此操作的一部分移动的行不会被捕获为更改表中的已删除行。 同样,如果将已预填充行的新分区添加到该表,这些行将不会反映在更改表中。 当更改被应用程序使用并应用于目标时,这可能会导致数据不一致。

注意

如果在 SQL Server 2008 R2 上通过当前版本启用分区切换,稍后可能还需要执行拆分和合并操作。 在复制或启用了 CDC 的表上执行拆分或合并操作之前,请确保所涉及的分区没有任何待定的复制命令。 你还应确保在拆分和合并操作期间,该分区上没有执行任何 DML 操作。 如果存在日志读取器或 CDC 捕获作业尚未处理的事务,或者执行拆分或合并操作期间在复制或启用了 CDC·的表的分区上执行 DML 操作(涉及同一个分区),将导致日志读取器代理或 CDC 捕获作业出现处理错误(错误 608 - 找不到分区 ID 的目录条目)。 为了更正错误,可能需要重新初始化订阅或禁用该表或数据库上的 CDC。

返回代码值

0 (成功) 或 1 (失败)

结果集

备注

在可以对表启用变更数据捕获之前,必须先对数据库启用变更数据捕获。 若要确定是否为更改数据捕获启用数据库,请查询 sys.databases 目录视图中的is_cdc_enabled列。 若要启用数据库,请使用 sys.sp_cdc_enable_db 存储过程。

对表启用变更数据捕获时,将生成一个更改表以及一个或两个查询函数。 更改表充当捕获进程从事务日志中提取的源表更改的存储库。 查询函数则用于从更改表中提取数据。 这些函数的名称以下列方式派生自 capture_instance 参数:

  • 所有更改函数: cdc.fn_cdc_get_all_changes_<capture_instance>

  • 净更改函数: cdc.fn_cdc_get_net_changes_<capture_instance>

如果源表是要为更改数据捕获启用的数据库中的第一个表,并且数据库不存在事务发布,则sys.sp_cdc_enable_table还会为数据库创建捕获和清理作业。 它将 sys.tables 目录视图中的 is_tracked_by_cdc 列设置为 1。

注意

SQL Server 代理在为表启用更改数据捕获时不必运行。 但是,除非SQL Server 代理正在运行,否则捕获进程不会处理事务日志并将条目写入更改表。

权限

要求具有 db_owner 固定数据库角色中的成员资格。

示例

A. 通过仅指定必需的参数来启用变更数据捕获

下面的示例对 HumanResources.Employee 表启用了变更数据捕获。 仅指定了必需的参数。

USE AdventureWorks2012;  
GO  
EXECUTE sys.sp_cdc_enable_table  
    @source_schema = N'HumanResources'  
  , @source_name = N'Employee'  
  , @role_name = N'cdc_Admin';  
GO  

B. 通过指定其他可选参数启用变更数据捕获

下面的示例对 HumanResources.Department 表启用了变更数据捕获。 指定了除 @allow_partition_switch 以外的所有参数。

USE AdventureWorks2012;  
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  

另请参阅

sys.sp_cdc_disable_table (Transact-SQL)
sys.sp_cdc_help_change_data_capture (Transact-SQL)
<cdc.fn_cdc_get_all_changes_capture_instance> (Transact-SQL)
<cdc.fn_cdc_get_net_changes_capture_instance> (transact-SQL)
sys.sp_cdc_help_jobs (Transact-SQL)