如何通过SQL Server Change Tracking获取发票行单价全量变更记录？

我有一个发票模块，发票行包含*Unit Price（单价）*字段，希望查看该单价的所有变更交易记录，但当前仅能获取最后一次变更。期望得到如下格式的变更列表：

当前使用的SQL语句如下，请问如何调整以实现需求？

DECLARE @last_synchronization_version bigint;

SELECT
    CT.ID, P.GENELTOPLAM,
    CT.SYS_CHANGE_OPERATION, 
    CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM 
    FATURA AS P
RIGHT OUTER JOIN 
    CHANGETABLE(CHANGES FATURA, @last_synchronization_version) AS CT ON P.ID = CT.ID

要获取单价字段的完整变更历史，需针对SQL做以下核心调整，前提是你已经为FATURA表启用了SQL Server的变更数据捕获（CDC）：

1. 改用CDC捕获表获取历史值 #

原查询关联原表只能拿到最新数据，需直接查询CDC自动生成的捕获表（命名格式为cdc.[表名]_CT），该表存储了每一次变更的旧值、新值及操作类型。

2. 过滤仅单价字段的变更 #

通过sys.fn_cdc_is_bit_set函数判断单价列（GENELTOPLAM）是否在本次变更中被修改，避免返回无关字段的变更记录。

3. 转换操作类型与日期格式 #

将CDC内置的操作编码转换为中文描述，把日志序列号（LSN）转换为可读的日期时间。

调整后的完整SQL #

DECLARE @last_synchronization_version bigint;

-- 获取单价字段的列ID，用于判断是否发生变更
DECLARE @genelToplamColId int = (
    SELECT column_id 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('FATURA') AND name = 'GENELTOPLAM'
);

SELECT
    CT.ID,
    -- 提取变更后的单价：插入取新值，更新取新值
    CASE 
        WHEN CT.__$operation = 2 THEN CT.GENELTOPLAM
        WHEN CT.__$operation = 4 THEN CT.GENELTOPLAM
    END AS 单价,
    -- 转换操作类型为中文
    CASE 
        WHEN CT.__$operation = 2 THEN '首次插入'
        WHEN CT.__$operation = 4 THEN '更新'
    END AS 操作,
    -- 将LSN转换为实际日期时间
    sys.fn_cdc_map_lsn_to_time(CT.__$start_lsn) AS 日期
FROM 
    cdc.FATURA_CT AS CT
WHERE 
    -- 仅保留单价字段变更的记录
    sys.fn_cdc_is_bit_set(@genelToplamColId, CT.__$update_mask) = 1
    -- 可选：过滤指定版本之后的变更（若需要增量同步）
    AND (@last_synchronization_version IS NULL OR CT.__$start_lsn > @last_synchronization_version)
-- 按发票ID和变更时间排序，确保历史顺序正确
ORDER BY 
    CT.ID, CT.__$start_lsn;

关键说明 #

• CDC启用检查：如果未启用CDC，需先执行EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'FATURA', @role_name = NULL;开启（需管理员权限）。
• 操作类型说明：CDC捕获表的__$operation字段：2=插入，3=更新（旧值），4=更新（新值），这里我们只取新值展示变更后的状态。
• 无CDC替代方案：若无法启用CDC，可手动创建一张FATURA_PRICE_HISTORY历史表，在FATURA表的更新触发器中插入单价变更记录，后续直接查询该历史表即可。

内容的提问来源于stack exchange，提问作者Beyaz Cennet