SQL Server 2008 變更資料捕獲 (CDC) 完整實作指南

CDC 簡介

SQL Server 2008 提供內建的非同步變更資料捕獲功能，無需撰寫觸發器即可追蹤使用者資料表的 INSERT/UPDATE/DELETE 操作，效能開銷極低，適用於 OLTP 資料同步至資料倉庫等 ETL 案例。

啟用資料庫層級 CDC

-- 檢查是否已啟用
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'CDC_TEST_DB'

-- 啟用
USE CDC_TEST_DB
EXEC sys.sp_cdc_enable_db

啟用後會建立 cdc 結構描述與系統資料表（captured_columns、change_tables、ddl_history、lsn_time_mapping 等）。

啟用資料表層級 CDC

-- 建立測試表
CREATE TABLE TB_CDC_USER (
    USERID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    NAME VARCHAR(20) NOT NULL,
    ADDRESS VARCHAR(100) NOT NULL
)
INSERT TB_CDC_USER (NAME, ADDRESS) VALUES
('香蕉','dss省fdfd市'),('鴿子','山東省青島市'),('水哥','江蘇省蘇州市'),('土豆','XX省XX市')

-- 啟用表追蹤，支援淨變更查詢
EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'TB_CDC_USER',
    @role_name = NULL,
    @supports_net_changes = 1,
    @filegroup_name = DEFAULT

此時會建立追蹤表 cdc.dbo_TB_CDC_USER_CT，包含 __$start_lsn、__$operation（1=刪除,2=插入,3=更新前,4=更新後）、__$update_mask 等中繼欄位。

驗證追蹤狀態

SELECT is_tracked_by_cdc FROM sys.tables WHERE name = 'TB_CDC_USER' AND schema_id = SCHEMA_ID('dbo')
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'TB_CDC_USER'

變更操作測試

插入、更新、刪除操作後，查詢 cdc.dbo_TB_CDC_USER_CT 可見對應的操作記錄：

• 插入：__$operation = 2
• 更新：產生兩筆，3（舊值）與 4（新值）
• 刪除：__$operation = 1

查詢變更資料

使用 LSN 時間對應函數

DECLARE @begin_lsn BINARY(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '2010-05-20 14:00:00')
DECLARE @end_lsn BINARY(10) = sys.fn_cdc_map_time_to_lsn('largest less than or equal', '2010-05-20 16:00:00')

取得所有變更

SELECT __$operation, sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS change_time, USERID, NAME, ADDRESS
FROM cdc.fn_cdc_get_all_changes_dbo_TB_CDC_USER(@begin_lsn, @end_lsn, 'all')

參數 'all' 回傳所有版本；'all update old' 只回傳更新前後最終版本。

取得淨變更

SELECT 
    CASE __$operation WHEN 1 THEN '刪除' WHEN 2 THEN '插入' WHEN 4 THEN '更新' WHEN 5 THEN '合併' END AS operation,
    __$update_mask, USERID, NAME, ADDRESS
FROM cdc.fn_cdc_get_net_changes_dbo_TB_CDC_USER(@begin_lsn, @end_lsn, 'all with mask')

淨變更只回傳每個主鍵最終狀態，__$operation = 5 代表插入或更新合併。

解析更新遮罩

SELECT 
    sys.fn_cdc_is_bit_set(sys.fn_cdc_get_column_ordinal('dbo_TB_CDC_USER','NAME'), __$update_mask) AS name_updated,
    sys.fn_cdc_is_bit_set(sys.fn_cdc_get_column_ordinal('dbo_TB_CDC_USER','ADDRESS'), __$update_mask) AS address_updated,
    USERID, NAME, ADDRESS
FROM cdc.fn_cdc_get_all_changes_dbo_TB_CDC_USER(@from_lsn, @to_lsn, 'all')
WHERE __$operation = 4

可精確得知哪個欄位被更新。

LSN 邊界操作函數

• sys.fn_cdc_increment_lsn / decrement_lsn
• sys.fn_cdc_get_max_lsn / get_min_lsn

停用 CDC

EXEC sys.sp_cdc_disable_table 'dbo', 'TB_CDC_USER', 'all'
EXEC sys.sp_cdc_disable_db