您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2008 變更追蹤功能深度解析與實作指南

編輯：關於SqlServer

前言：為什麼需要變更追蹤？

傳統方案：觸發器 + 時間戳欄位 + 追蹤表 → 效能差、鎖爭用、維護複雜。
SQL Server 2008 引入 Change Tracking (變更追蹤)：輕量、同步記錄、開發者友善、支援雙向同步 (配合 ADO.NET Sync Services)。

核心特性比較

特性	Change Tracking	Change Data Capture (CDC)	觸發器方案
記錄內容	主鍵 + 操作類型 (+ 選擇性欄位)	完整前後映像	自訂
儲存成本	低	高	中高
效能影響	極低 (同步提交)	低 (非同步擷取)	高 (觸發器遞迴)
支援截斷	自動清除 (可設保留期)	需手動管理	需手動
適用場景	同步應用、單向/雙向同步	稽核、資料擷取、E TL	複雜業務邏輯驗證
查詢介面	CHANGETABLE() / 變更追蹤函數	CDC 函數	自訂表

啟用變更追蹤：兩層級設定

1. 資料庫層級


ALTER DATABASE AdventureWorks2008
SET CHANGE_TRACKING = ON
(
    AUTO_CLEANUP = ON,           -- 自動清除過期變更
    CHANGE_RETENTION = 8 HOURS   -- 保留 8 小時 (最小 1 分鐘)
);
GO

內部系統表 sys.syscommittab 記錄每筆提交的交易 (透過 sys.dm_tran_commit_table 檢視)
每 30 分鐘背景任務清除過期記錄

2. 資料表層級


-- 基本啟用 (需有主鍵)
ALTER TABLE HumanResources.Department
ENABLE CHANGE_TRACKING;
GO

-- 進階：啟用欄位層級追蹤
ALTER TABLE HumanResources.Department
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
GO

建立內部變更追蹤表 change_tracking_[object_id] (僅 DAC 可見)
欄位：sys_change_xdes_id、sys_change_op (I/U/D)、sys_change_columns、sys_change_context、k_PK名_序號

查詢變更資料

1. 取得同步版本錨點


-- 取得目前資料庫最新認可版本
DECLARE @lastSyncVersion BIGINT = CHANGE_TRACKING_CURRENT_VERSION();

2. 查詢指定版本後的變更


-- 基本查詢：取得變更的主鍵與操作類型
SELECT 
    CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_VERSION,
    CT.SYS_CHANGE_CREATION_VERSION,
    CT.SYS_CHANGE_CONTEXT,
    D.DepartmentID,
    D.Name,
    D.GroupName
FROM CHANGETABLE(CHANGES HumanResources.Department, @lastSyncVersion) AS CT
LEFT JOIN HumanResources.Department AS D 
    ON CT.DepartmentID = D.DepartmentID
WHERE CT.SYS_CHANGE_OPERATION IN ('I', 'U', 'D');

3. 欄位層級變更查詢 (需 TRACK_COLUMNS_UPDATED = ON)


SELECT 
    CT.DepartmentID,
    CT.SYS_CHANGE_OPERATION,
    -- 判斷特定欄位是否被更新
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('HumanResources.Department'), 'GroupName', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) AS GroupNameChanged
FROM CHANGETABLE(CHANGES HumanResources.Department, @lastSyncVersion) AS CT;

4. 應用程式內容追蹤


DECLARE @originator_id VARBINARY(128) = CAST('MyApp_SyncEngine' AS VARBINARY(128));

WITH CHANGE_TRACKING_CONTEXT (@originator_id)
UPDATE HumanResources.Department
SET GroupName = 'Accounting'
WHERE DepartmentID = 10;

-- 後續查詢 SYS_CHANGE_CONTEXT 可識別來源應用

停用與清理


-- 1. 先停用所有資料表
SELECT 'ALTER TABLE ' + OBJECT_NAME(object_id) + ' DISABLE CHANGE_TRACKING;'
FROM sys.change_tracking_tables;
-- 執行產生的腳本

-- 2. 停用資料庫層級
ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = OFF;
GO

限制與注意事項

不支援：截斷資料表 (TRUNCATE TABLE)、切換分區 (SWITCH PARTITION) 的變更記錄
不適合稽核：僅記錄淨變更，無法取得中間狀態、無時間戳、無使用者身分 (需自行透過 CONTEXT 記錄)
主鍵必要：無主鍵資料表無法啟用
版本計數器：資料庫層級單一遞增，跨表同步需共用版本錨點
同步邏輯：應用端需持久化 @lastSyncVersion，重啟後續接同步

結語

Change Tracking 是 SQL Server 2008 為同步應用量身打造的輕量級變更擷取機制。低成本、高效能、易開發，配合 ADO.NET Sync Framework 可快速建立離線/行動同步方案。但非稽核用途、不記錄完整歷史，選型時務必區分 CDC 與 Change Tracking 的定位差異。