无主键/时间戳的On-prem SQL Server CDC表增量加载问题咨询

针对你提到的没有主键和增量时间戳的On-prem SQL Server CDC表，结合自托管IR的Copy Activity，可以通过以下几种方案实现增量加载：

方案1：利用CDC系统列__$start_lsn做增量判断 #

SQL Server的CDC表默认自带**$start_lsn**（日志序列号）、**$seqval**（序列值）等系统列，这些值是严格递增的，完全可以作为增量同步的依据：

• 首次同步：全量拉取CDC表数据，同步完成后，查询并记录本次同步的最大__$start_lsn值（可以存储在源SQL Server的一个自定义控制表中，或者ADLS的文本文件里）
• 增量同步：
  1. 先读取上次记录的__$start_lsn值
  2. 在Copy Activity的源查询中添加过滤条件：

     SELECT * FROM [CDC表名] WHERE __$start_lsn > 0x[上次记录的LSN十六进制值]
     

  3. 同步完成后，更新控制表中的LSN为本次同步的最大值

注意：LSN是二进制类型，存储时可以转为十六进制字符串（比如用CONVERT(VARCHAR(MAX), __$start_lsn, 1)），避免二进制存储的兼容性问题。

方案2：结合CDC系统表cdc.lsn_time_mapping做时间维度增量 #

如果需要按时间维度控制增量范围，可以利用SQL Server CDC的系统表cdc.lsn_time_mapping，它关联了LSN和事务的结束时间：

• 首次同步后，记录本次同步的截止时间
• 后续增量同步时：
  1. 根据上次的截止时间，查询对应的最大LSN：

     SELECT MAX(start_lsn) FROM cdc.lsn_time_mapping WHERE tran_end_time < '上次同步截止时间'
     

  2. 用这个LSN作为过滤条件拉取CDC表的增量数据
  3. 更新控制表中的截止时间为当前同步的时间

方案3：哈希值对比兜底（仅适合小表） #

如果上述系统列不可用（极端情况），可以采用全量扫表+哈希对比的方式：

• 每次同步时，先将源CDC表全量导入到临时表
• 计算每行的哈希值（结合所有非系统列生成）：

  SELECT *, HASHBYTES('SHA2_256', CONCAT(ISNULL(col1,''), ISNULL(col2,''), ...)) AS row_hash FROM [临时表名]
  

• 将临时表的哈希值与目标表的哈希值对比，筛选出新增或变化的记录写入目标表

这个方法性能较低，仅适合数据量较小的表。

额外注意事项 #

• 确保源SQL Server的CDC捕获作业正常运行，避免CDC日志堆积或丢失
• 控制表的LSN/时间更新要保证原子性，比如用事务包裹查询和更新操作，防止重复同步或数据遗漏
• 对于CDC表中的__$operation列，可以用来区分操作类型（1=删除，2=插入，3=更新前，4=更新后），按需处理目标表的同步逻辑（比如删除操作在无主键的情况下，可能需要结合哈希值匹配目标行）

内容的提问来源于stack exchange，提问作者Yami Mahō