sys.sp_cdc_enable_table (Transact-SQL)

项目
12/22/2023

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

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

语法

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_name 为 sysname ，必须指定。如果显式设置为 NULL，则没有控制角色用于限制对更改数据的访问。

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

[ @capture_instance = ] 'capture_instance'

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

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

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

[ @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 上启用分区切换，则将来可能还需要拆分和合并操作。在对启用了副本 (replica)或 CDC 的表执行拆分或合并操作之前，请确保有问题的分区没有任何挂起的副本 (replica)命令。你还应确保在拆分和合并操作期间，该分区上没有执行任何 DML 操作。如果日志读取器或 CDC 捕获作业未处理事务，或者在执行拆分或合并操作时对启用了副本 (replica) 或 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_cdc1。

为表启用 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

sys.sp_cdc_enable_table (Transact-SQL)

语法

参数

[ @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 = ] N'captured_column_list'

[ @filegroup_name = ] 'filegroup_name'

[ @allow_partition_switch = ] 'allow_partition_switch'

返回代码值

结果集

注解

权限

示例

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

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

反馈

反馈

其他资源

sys.sp_cdc_enable_table (Transact-SQL)

语法

参数

[ @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 = ] N'captured_column_list'

[ @filegroup_name = ] 'filegroup_name'

[ @allow_partition_switch = ] 'allow_partition_switch'

返回代码值

结果集

注解

权限

示例

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

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

相关内容

反馈

反馈

其他资源