sys.sp_cdc_enable_table (Transact-SQL)

适用范围:SQL Server

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

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

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 = ] N'captured_column_list' ]
    [ , [ @filegroup_name = ] 'filegroup_name' ]
    [ , [ @allow_partition_switch = ] 'allow_partition_switch' ]
[ ; ]

参数

[ @source_schema = ] 'source_schema'

源表所属的架构的名称。 @source_schema是 sysname,没有默认值,不能NULL

[ @source_name = ] 'source_name'

要对其启用更改数据捕获的源表的名称。 @source_name是 sysname,没有默认值,不能NULL

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

[ @role_name = ] 'role_name'

用于限制对更改数据的访问权限的数据库角色的名称。 @role_namesysname ,必须指定。 如果显式设置为 NULL,则不会使用任何限制角色来限制对更改数据的访问。

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

[ @capture_instance = ] 'capture_instance'

用于命名特定于实例的变更数据捕获对象的捕获实例的名称。 @capture_instance是 sysname,不能NULL

如果未指定,则该名称将从源架构名称加上源表名称中派生而来,格式为 <schemaname>_<sourcename>@capture_instance不能超过 100 个字符,并且必须在数据库中是唯一的。 无论指定还是派生, @capture_instance 都剪裁字符串右侧的任何空格。

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

[ @supports_net_changes = ] supports_net_changes

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

  • 如果 0仅生成用于查询所有更改的支持函数。
  • 如果 1生成查询净更改所需的函数。

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

如果 @supports_net_changes 设置为 1,则会在更改表上创建一个额外的非聚集索引,并创建净更改查询函数。 由于需要维护此索引,因此启用净更改可能会对 CDC 性能产生负面影响。

[ @index_name = ] 'index_name'

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

[ @captured_column_list = ] N'captured_column_list'

标识将包括在更改表中的源表列。 @captured_column_list是 nvarchar(max),可以是 NULL。 如果 NULL包含所有列,则更改表中包括所有列。

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

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

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

[ @filegroup_name = ] 'filegroup_name'

要用于为捕获实例创建的更改表的文件组。 @filegroup_name是 sysname,可以是 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 上启用分区切换,则将来可能还需要拆分和合并操作。 在对复制的表或 CDC 启用的表执行拆分或合并操作之前,请确保有问题的分区没有任何挂起的复制命令。 你还应确保在拆分和合并操作期间,该分区上没有执行任何 DML 操作。 如果日志读取器或 CDC 捕获作业未处理事务,或者在执行拆分或合并操作时对启用了 CDC 的表的分区执行 DML 操作(涉及同一分区),则可能会导致处理错误(错误 608 - 没有找到分区 ID 的目录条目)和日志读取器代理或 CDC 捕获作业。 为了更正错误,可能需要重新初始化订阅或禁用该表或数据库上的 CDC。

返回代码值

0(成功)或 1(失败)。

结果集

无。

注解

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

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

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

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

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

权限

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

示例

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

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

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