SQL Server变更追踪如何仅识别单行的最新更新列？

问题场景 #

创建表 #

CREATE TABLE [dbo].[Products](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Manufacturer] [nvarchar](max) NULL,
    [Country] [nvarchar](max) NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([Id] ASC)
);

插入测试数据 #

INSERT INTO Products ([Name],[Manufacturer],[Country])
VALUES ('phone', 'sony', 'japan');

启用变更追踪 #

ALTER DATABASE [Test]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 90 MINUTES, AUTO_CLEANUP = ON);

ALTER TABLE [dbo].[Products]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

两次更新操作 #

1. 第一次更新：

UPDATE Products SET Manufacturer = 'Sega' WHERE Name = 'phone';

此时查询仅Manufacturer列标记为变更，符合预期。

2. 第二次更新同一行：

UPDATE Products SET Country = 'USA' WHERE Name = 'phone';

再次查询时，Manufacturer和Country列均被标记为变更，且两次更新的SYS_CHANGE_VERSION相同，无法单独处理最新更新的列。

问题原因 #

SQL Server变更追踪（CT）的TRACK_COLUMNS_UPDATED = ON是累积记录从上次同步版本到当前版本之间，该行所有被修改过的列，它以行版本为单位记录变更，而非区分每一次单独的更新操作。如果查询时使用的起始版本是第一次更新前的版本，系统会返回该版本到当前版本的所有变更列，导致历史变更列被重复标记。

解决方案 #

方案1：基于增量同步版本获取最新变更 #

核心思路是每次处理完变更后，记录当前的SYS_CHANGE_VERSION，下次查询时以此版本作为起始点，只获取从上一次同步以来的新变更。

操作步骤：

1. 初始化同步版本：

DECLARE @last_sync_version BIGINT = CHANGE_TRACKING_CURRENT_VERSION();

2. 第一次更新后查询变更：

SELECT 
    p.Id,
    p.Name,
    p.Manufacturer,
    p.Country,
    ct.SYS_CHANGE_VERSION,
    -- 检查Manufacturer列是否变更
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Products'), 'Manufacturer', 'ColumnId'), ct.SYS_CHANGE_COLUMNS) AS Manufacturer_Changed,
    -- 检查Country列是否变更
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Products'), 'Country', 'ColumnId'), ct.SYS_CHANGE_COLUMNS) AS Country_Changed
FROM Products p
JOIN CHANGETABLE(CHANGES Products, @last_sync_version) ct ON p.Id = ct.Id;

-- 更新同步版本为当前最新版本
SET @last_sync_version = CHANGE_TRACKING_CURRENT_VERSION();

此时仅会返回Manufacturer列的变更记录。

3. 第二次更新后查询变更：
   使用更新后的@last_sync_version执行相同查询，此时仅会返回Country列的变更记录，满足只处理最新更新列的需求。

方案2：使用变更数据捕获（CDC）追踪单次操作 #

如果需要严格区分每一次单独的更新操作（而非行的累积变更），可以使用SQL Server的变更数据捕获（CDC），它会记录每一次DML操作的详细信息，包括操作类型、修改的列、操作时间等。

启用CDC步骤：

1. 启用数据库级CDC：

ALTER DATABASE Test
SET CDC ON;

2. 对目标表启用CDC：

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Products',
    @role_name = NULL,
    @captured_column_list = N'Id,Name,Manufacturer,Country',
    @supports_net_changes = 1;

3. 查询单次更新的变更：

SELECT 
    __$operation, -- 1=删除, 2=插入, 3=更新前数据, 4=更新后数据
    __$start_lsn, -- 唯一标识单次操作的LSN
    Id,
    Name,
    Manufacturer,
    Country
FROM cdc.dbo_Products_CT
ORDER BY __$start_lsn DESC;

通过__$operation=4可以获取每次更新后的列值，结合__$start_lsn可以区分不同的更新操作，从而单独处理每一次修改的列。

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