在sql server 2022中,捕获实时变化数据的核心做法是启用变更数据捕获(cdc)功能。你需要对目标表执行 sys.sp_cdc_enable_table 存储过程来开启追踪,随后通过查询系统视图 cdc.fn_cdc_get_all_changes_...() 或 cdc.fn_cdc_get_net_changes_...() 来获取增量数据。此方法依赖事务日志,能精确记录每一行的插入、更新和删除操作。

如何配置并验证表的CDC状态
要开始使用,必须先在数据库级别启用CDC,然后针对具体表进行配置。配置完成后,可以通过查询 sys.tables 中的 is_tracked_by_cdc 列来验证状态。如果该列为1,说明该表已处于监控之下,任何DML操作都会被记录到内部表中。

-- 1. 在数据库级别启用CDC
EXECUTE sys.sp_cdc_enable_db;
-- 2. 为特定表启用CDC
-- @role_name 指定访问CDC数据的角色
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Orders',
@role_name = N'cdc_admin';
-- 3. 验证配置是否生效
SELECT name, is_tracked_by_cdc
FROM sys.tables
WHERE name = 'Orders';运行上述代码后,sys.tables 将返回 Orders 表的 is_tracked_by_cdc 值为 1。此时,向 Orders 表插入或更新数据,系统会自动生成对应的CDC记录。
如何查询具体的变更行数据
获取变更数据主要依靠CDC函数。最常用的是 cdc.fn_cdc_get_all_changes_<capture_instance></capture_instance>,它返回指定LSN范围内的所有变更行。你需要提供起始和结束的LSN(Log Sequence Number),并指定操作类型掩码以过滤只读需要的操作(如仅查看插入)。结果集中包含 $operation 列,其中1代表删除,2代表插入,3代表更新前值,4代表更新后值。

-- 定义变量用于获取当前LSN范围
DECLARE @from_lsn BINARY(10), @to_lsn BINARY(10);
-- 获取起始LSN(当前时间点之前的LSN)
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', GETDATE());
-- 获取结束LSN(当前时间点)
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE());
-- 查询所有变更
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Orders(
@from_lsn,
@to_lsn,
N'all update old'
); -- 'all' 表示返回所有操作类型执行该查询将返回一个结果集,每行代表一次变更。若 $operation 为2,则是新插入的数据;若为3或4,则是被修改的数据(3为旧值,4为新值)。通过这种方式,你可以实时同步数据到下游系统。
当表中没有发生DML操作时CDC函数返回什么
如果在指定的LSN范围内,目标表没有任何INSERT、UPDATE或DELETE操作,CDC函数不会报错,而是返回一个空的结果集。这意味着没有新的变更记录可供处理。开发者在编写ETL逻辑时,应当检查返回的行数,如果为空,则跳过后续的数据转换步骤,以避免不必要的资源消耗。

















