SQL Server 2008 變更資料擷取 (CDC) 實戰教學 (三)：結構描述變更對捕獲欄位影響與重新啟用 CDC 擷取全欄位

實驗場景

基於 SQL Server 2008 11 月 CTP，驗證資料表結構變更 (加欄位) 後，CDC 擷取行為與如何調整捕獲欄位清單。

步驟 1-4：建立 CDCDB、Employee 表、啟用資料庫/資料表 CDC

CREATE DATABASE CDCDB;
USE CDCDB;
CREATE TABLE Employee (ID int NOT NULL, Name varchar(100) NULL, CONSTRAINT Employee_PK PRIMARY KEY CLUSTERED (ID));
EXEC sys.sp_cdc_enable_db_change_data_capture;
EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema='dbo', @source_name='Employee', @role_name='cdc_Employee';

步驟 5：新增欄位 (CDC 啟用後)

ALTER TABLE Employee ADD Address varchar(500);
ALTER TABLE Employee ADD Salary money;
ALTER TABLE Employee ADD Bonus money;

步驟 6：插入資料並執行 DML

INSERT INTO Employee VALUES (1, 'DancingDoll', '...', 60000, 1000);
... (共 5 筆)
UPDATE Employee SET Name = 'test' WHERE ID = 5;
DELETE Employee WHERE ID IN (3,4);

步驟 7：查詢 DDL 歷程

SELECT * FROM cdc.ddl_history;

記錄 3 筆 ALTER TABLE 命令。

步驟 8：查詢變更資料

SELECT CASE __$operation WHEN 1 THEN 'Deleting' WHEN 2 THEN 'Inserting' WHEN 3 THEN 'ValueBeforeUpdate' WHEN 4 THEN 'ValueAfterUpdate' END, __$update_mask, ID, Name
FROM cdc.dbo_Employee_CT;

結果顯示：僅原始啟用 CDC 時存在的 ID、Name 兩欄被監控，後加入的 Address、Salary、Bonus 未被捕獲。

步驟 9-10：重新設定捕獲所有欄位

-- 停用現有 CDC
EXEC sys.sp_cdc_disable_table_change_data_capture @source_schema='dbo', @source_name='Employee', @capture_instance='dbo_Employee';
-- 重新啟用並指定捕獲欄位清單
EXEC sys.sp_cdc_enable_table_change_data_capture
    @source_schema='dbo', @source_name='Employee', @role_name='cdc_Employee',
    @captured_column_list=N'ID,Name,Salary,Bonus';

再次查詢變更表，指定欄位皆被監控。

結語

CDC 啟用後新增欄位預設不被捕獲，需停用並重新啟用 CDC 並指定 @captured_column_list 以擴展監控範圍，靈活應對結構描述演進。