[SQL SERVER][Memo]如何啟用變更追蹤
看到網友詢問，所以便將自己實作的過程記錄下來，順便再加深自己熟悉度。
早期自己有大概介紹過CDC，那Change Tracking聽上去似乎也是做同樣的事，兩者又有什麼差異呢?
CDC(Change Data Capture)
透過對交易日誌檔的非同步讀取，記錄DML操作的發生時間、類型和實際影響的資料變化，
然後將這些資料記錄到啟用CDC時所建立的資料表中。透過cdc相關SP，
可以取得詳細的資料變化情況。由於是非同步讀取的，
因此對整體性能的影響不大，遠小於透過Trigger實現的資料變化記錄。
CT(Change Tracking)
CT僅記錄DML操作的發生時間、類型和影響的資料列(不同於CDC，CDC記錄資訊相當多且詳細)，
使用者透過上次同步的版號來取得從上次到現在的變更記錄。
CT主要透過對要執行的DML分析來取得變更記錄，
而不是去讀交易日誌檔。因此CT對效能的影響應比CDC來得小。
實作
Step1建立測試Table(變更追蹤要求資料表必須有主索引鍵)
CREATE TABLE dbo.mytbl(col1 INT IDENTITY(1,1) PRIMARY KEY,
col2 NVARCHAR(50),
col3 NVARCHAR(50))
GO
Step2.啟用資料庫和資料表變更追蹤(請先啟動資料庫變更追蹤)
--啟用資料庫變更追蹤
ALTER DATABASE demotest
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)GO--啟用資料表變更追蹤
ALTER TABLE dbo.mytbl
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
啟用變更追蹤時，系統會針對資料表中受到 DML 作業影響的所有資料欄位已更新的資訊存到系統的變更追蹤資料表。
Example:查詢新增資料結果
--宣告變數
DECLARE @last_sync_version BIGINT;--同步最後版本資訊
SELECT @last_sync_version = CHANGE_TRACKING_CURRENT_VERSION();--新增資料
INSERT dbo.mytbl VALUES
('test1','test1'),
('test2','test2'),
('test3','test3'),
('test4','test4')--查詢變更結果
SELECT
CT.col1, CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXTFROM
CHANGETABLE(CHANGES dbo.mytbl, @last_sync_version) AS CT
針對PK欄位可以發現剛剛所新增的4筆資料。
Example:查詢更新資料結果
--宣告變數
DECLARE @last_sync_version BIGINT;--同步最後版本資訊
SELECT @last_sync_version = CHANGE_TRACKING_CURRENT_VERSION();--更新資料
UPDATE dbo.mytbl SET col2='good1' WHERE col1=1
UPDATE dbo.mytbl SET col2='good2' WHERE col1=2
UPDATE dbo.mytbl SET col2='good11' WHERE col1=1
UPDATE dbo.mytbl SET col2='good22' WHERE col1=2
--查詢變更結果
SELECT
CT.col1, CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXTFROM
CHANGETABLE(CHANGES dbo.mytbl, @last_sync_version) AS CT
雖然執行更新了4次，但只記錄最後兩次的變更資訊。
參考