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

SQL Server 2008 變更資料擷取 (CDC) 專家指南：啟用、查詢淨變更/所有變更、定期擷取實作

編輯：關於SqlServer

CDC 核心價值

Change Data Capture (CDC) 記錄資料表 INSERT/UPDATE/DELETE 修改活動。優於傳統查詢 DATETIME 欄位：可偵測實體刪除、識別具體變更欄位、支援複雜修改歷程查詢。

啟用與設定 (基於 2008 年 2 月 CTP)

1. 資料庫層級啟用 (需 sysadmin)


DECLARE @rc INT; EXEC @rc = sys.sp_cdc_enable_db; SELECT @rc;
-- sys.databases 新增 is_cdc_enabled 欄位驗證
SELECT name, is_cdc_enabled FROM sys.databases;

2. 建立來源表


CREATE TABLE dbo.customer (
    id INT IDENTITY NOT NULL,
    name VARCHAR(50) NOT NULL,
    state VARCHAR(2) NOT NULL,
    CONSTRAINT pk_customer PRIMARY KEY CLUSTERED (id)
);

3. 資料表層級啟用 (需 db_owner + SQL Agent 執行中)


EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'customer',
    @role_name = 'CDCRole',
    @supports_net_changes = 1;
-- sys.tables 新增 is_tracked_by_cdc 驗證
SELECT name, type, type_desc, is_tracked_by_cdc FROM sys.tables;

參數說明：@role_name 控制讀取權限 (不存在自動建立)；@supports_net_changes=1 需主鍵/唯一索引；一張表最多兩個捕獲實例 (@capture_instance)。

4. CDC 建立的物件

cdc 結構描述與使用者
系統表：captured_columns, change_tables, ddl_history, index_columns, lsn_time_mapping
Agent 作業：cdc.[dbname]_capture (掃描記錄檔)、cdc.[dbname]_cleanup (定期清理)
查詢函數與預存程序

5. 停用 CDC


-- 資料表層級
EXEC sys.sp_cdc_disable_table @source_schema='dbo', @source_name='customer', @capture_instance='dbo_customer';
-- 資料庫層級
DECLARE @rc INT; EXEC @rc = sys.sp_cdc_disable_db; SELECT @rc;

CDC 擷取示範

執行 DML


INSERT customer VALUES ('abccompany','md'), ('xyzcompany','de'), ('xoxcompany','va');
UPDATE customer SET state='pa' WHERE id=1;
DELETE FROM customer WHERE id=3;

查詢變更


DECLARE @begin_lsn BINARY(10), @end_lsn BINARY(10);
SELECT @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer');
SELECT @end_lsn = sys.fn_cdc_get_max_lsn();

-- 淨變更 (依主鍵彙總)
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_customer(@begin_lsn, @end_lsn, 'all');
-- 所有變更明細
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customer(@begin_lsn, @end_lsn, 'all');

結果解讀：__$operation 1=DELETE, 2=INSERT, 3=UPDATE(前), 4=UPDATE(後)；__$update_mask 二進位標識變更欄位 (INSERT/DELETE 全標記)。需傳遞 'all update old' 才看更新前值。可用 sys.sp_cdc_get_captured_columns 查詢欄位序數。

定期擷取實作模式

建立 LSN 追蹤表與函數


CREATE TABLE dbo.customer_lsn (last_lsn BINARY(10));
CREATE FUNCTION dbo.get_last_customer_lsn() RETURNS BINARY(10)
AS BEGIN
    DECLARE @last_lsn BINARY(10);
    SELECT @last_lsn = last_lsn FROM dbo.customer_lsn;
    SELECT @last_lsn = ISNULL(@last_lsn, sys.fn_cdc_get_min_lsn('dbo_customer'));
    RETURN @last_lsn;
END;

增量擷取腳本


DECLARE @begin_lsn BINARY(10), @end_lsn BINARY(10);
SELECT @begin_lsn = dbo.get_last_customer_lsn();
SELECT @end_lsn = sys.fn_cdc_get_max_lsn();

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_customer(@begin_lsn, @end_lsn, 'all');
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customer(@begin_lsn, @end_lsn, 'all');

-- 更新/插入結束 LSN
UPDATE dbo.customer_lsn SET last_lsn = @end_lsn;
IF @@ROWCOUNT = 0 INSERT INTO dbo.customer_lsn VALUES (@end_lsn);

後續執行僅擷取上次 ETL 後的新變更。