使用 BINARY_CHECKSUM 偵測資料列變更

原理

BINARY_CHECKSUM(*) 會根據資料列所有可比較欄位計算二進位檢查碼。同一資料列在 UPDATE 後，檢查碼會改變，藉此快速判斷哪些列被修改。

語法

BINARY_CHECKSUM ( * | expression [ ,...n ] )

忽略不可比較類型：text、ntext、image、cursor、sql_variant。

實作範例

-- 1. 建立測試表並寫入初始資料
CREATE TABLE CheckRowIsUpdate (id INT, name VARCHAR(20));
INSERT INTO CheckRowIsUpdate VALUES (1,'aa'),(2,'bb'),(3,'cc'),(4,'dd');

-- 2. 將初始檢查碼存入暫存表
SELECT *, BINARY_CHECKSUM(*) AS BID INTO #CheckRowIsUpdate FROM CheckRowIsUpdate;

-- 3. 修改資料
UPDATE CheckRowIsUpdate SET name = 'checkupdate' WHERE id = 4;

-- 4. 比對找出變更列
SELECT * FROM #CheckRowIsUpdate A
WHERE EXISTS (
    SELECT 1 FROM CheckRowIsUpdate B
    WHERE A.id = B.id AND BINARY_CHECKSUM(*) <> A.BID
);
-- 結果：僅回傳 id=4 的列

應用場景

• ETL 增量抽取：比對來源系統與暫存區檢查碼，僅處理變更列。
• 快取失效：應用層快取資料列檢查碼，輪詢資料庫偵測變更。
• 審計觸發器：在觸發器中比對 INSERTED 與 DELETED 的檢查碼，記錄實際變更欄位。

限制

• 極小機率發生碼撞（不同資料產生相同檢查碼），極高可靠度需求可改用 CHECKSUM_AGG 或 HASHBYTES('MD5', ...)。
• 不支援大型物件（LOB）欄位。