初探SQL Server 2008 Change Tracking

前段時間評估了一下SQL Server 2008的CDC功能，總體發現CDC的開銷還是不小的，特別是對日誌檔案的讀。對於那些欄位數量較多而且需要跟蹤每個欄位值更新的使用者表，CDC的帶來的額外開銷則尤其明顯。因此繼續評估SQL Server 2008對資料更新跟蹤的技術方案。

在SQL Server 2008的聯機叢書中提到了，Change Tracking適用於單向或雙向的資料同步應用場景。在接下來的一些列試用中，我們就能深刻體會到這一點。

啟用Change Tracking

要使用Change Tracking，首先需要在資料庫級別啟用Change Tracking功能，我們可以通過兩種途徑啟用Change Tracking。

1. 使用T-SQL語句，例如要啟用TestCT資料庫的Change Tracking功能，可以通過提交如下語句
   ALTER DATABASE TestCT
   SET CHANGE_TRACKING = ON
   (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
2. 在SQL Server Management Studio中通過設定資料庫的屬性，Change Tracking功能在資料庫屬性對話方塊中擁有一個獨立的選項頁。

其中我們會注意到CHANGE_RETENTION引數和AUTO_CLEANUP引數。因為Change Tracking所有的資訊都會被新增到一張內部表中，所以這張內部表將會無限制地增長，如果我們不希望這張表的資料量一直增長，Change Tracking功能提供了一個後臺的程式自動對內部表進行清理，清理的依據就是CHANGE_RETENTION，每次清理程式執行的時候都會將超過CHANGE_RETENTION引數中設定時間的記錄刪除掉，而清理程式是否會執行則依賴於AUTO_CLEANUP引數。CHANGE_RETENTION引數的時間單位可以是分鐘、小時或天。

啟用資料庫級別的Change Tracking功能後，DBA就可以選擇哪些需要更新跟蹤的表了。我們同樣也有兩種方法在更新跟蹤的表上啟用Change Tracking功能：

1. 使用T-SQL語句，例如要啟用TestCT表上的更新跟蹤，我們可以提交如下語句
   ALTER TABLE TestCT
   ENABLE CHANGE_TRACKING
   WITH (TRACK_COLUMNS_UPDATED = ON)
2. 在SQL Server Management Studio中通過設定表的屬性，Change Tracking功能在表屬性對話方塊中擁有一個獨立的選項頁。

其中我們注意到有個引數是TRACK_COLUMN_UPDATED，當這個引數被設定為ON的時候，UPDATE語句提交後在內部表中將會記錄UPDATE語句影響了哪些列，對於INSERT和DELETE語句，則所有列都是被影響到的。

使用Change Tracking

Change Tracking提供的資訊可不像Change Data Capture那麼詳細，Change Data Capture可以提供每個事務影響到的資料的前像和後像。Change Tracking功能通過CHANGETABLE系統表來獲得更新的版本資訊。

CHANGETABLE有兩種用法，一種是CHANGETABLE(CHANGES)，一種是CHANGETABLE(VERSION)。前者用於返回某個sync_version後的資料變化情況，後者用於返回某行資料的最新的更改版本號。

CHANGETABLE(CHANGES)

這是一個表函式，語法結構為CHANGETABLE(CHANGES table, last_sync_version)，table引數為啟用Change Tracking功能的表名，last_sync_version引數是希望獲取更新的最小版本號。

table引數的值非常明白，我們希望獲得哪張表的更新資訊，就是這張表的名字了，而last_sync_version是影響返回結果集的主要因素。
舉個例子，我們用下面這段指令碼來詳細說明Change Tracking記錄下的資訊：

CREATE TABLE TestCT
(
    ID int PRIMARY KEY,
    Name varchar(50),
    Description varchar(200)
)
GO
ALTER TABLE TestCT
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
GO

這個時候TestCT表的Change Tracking功能就被啟用了，這張表上所有的資料更新都會被記錄到一張內部表中，接著我們就可以在TestCT表上提交各種DML語句了。
INSERT INTO TestCT VALUES (1, 'ABC', NULL)

這個時候我們可以通過CHANGETABLE函式來檢視TestCT的更新歷史
SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT
返回結果為

SYS_CHANGE _VERSION	SYS_CHANGE _CREATION_VERSION	SYS_CHANGE _OPERATION	SYS_CHANGE _COLUMNS	SYS_CHANGE _CONTEXT	ID
1	1	I	NULL	NULL	1

返回結果集中：

• SYS_CHANGE_VERSION是一個bigint的欄位，表示的當前這行資料最新的更新版本號。
• SYS_CHANGE_CREATION_VERSION代表的是當前資料行被插入資料表的更新版本號。
• SYS_CHANGE_OPERATION是一個nchar(1)的欄位，I代表Insert，U代表Update，D代表Delete。
• SYS_CHANGE_COLUMNS代表更新操作影響到了哪些資料列，這個欄位的結果是個varbinary(4100)。這個欄位只有在表上啟用Change Tracking時將TRACK_COLUMN_UPDATED選項設定為ON時才會返回有效值，並且對於INSERT和DELETE操作返回的都是NULL，因為DELETE和INSERT其實影響到了所有資料列，只有UPDATE操作才會返回值，這個欄位的值可以通過CHANGE_TRACKING_IS_COLUMN_IN_MASK()函式來解析。
• SYS_CHANGE_CONTEXT是一個varbinary(128)的欄位，這個欄位可以記錄資料更新的上下文環境資訊，不過上下文環境資訊需要在提交DML語句時顯式地通過WITH CHANGE_TRACK_CONTEXT語句提供。
• ID是TestCT表的主鍵欄位，因此如果TestCT的主鍵是內容為(ID, Name)的組合主鍵，則除了ID外，CHANGETABLE返回結果集中還會多一個Name欄位。

我們接著測試，
UPDATE TestCT SET Name = 'abc' WHERE ID = 1

提交完這句語句後，其實TestCT表已經有了兩次更新，一次是INSERT操作，一次是UPDATE操作，但是這兩次操作都是針對ID為1的這一行資料，這個時候我們在CHANGETABLE函式中通過不同的last_sync_version引數會得到不同的返回結果。

SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT
返回結果為

SYS_CHANGE _VERSION	SYS_CHANGE _CREATION_VERSION	SYS_CHANGE _OPERATION	SYS_CHANGE _COLUMNS	SYS_CHANGE _CONTEXT	ID
2	1	I	NULL	NULL	1

從返回結果看，主鍵值為1的這行資料最近的更新版本為2，而插入表時的更新版本為1，代表這行資料在插入資料表後已經有過更新，但是SYS_CHANGE_OPERATION仍然是I。原因是我們選取的last_sync_version引數值是0，代表我們上次同步時資料更新版本是0，而這行資料是在版本0後被插入表的，因此這行資料還沒有出現在同步的目的表中，因此在我們進行同步時首先要處理INSERT操作，而被INSERT到源表中的原始記錄已經被更新了(在版本2時)，因此我們只能將INSERT操作和UPDATE操作進行合併，直接取得UPDATE後的資料行寫入目的表。

但是如果我們提交的語句是
SELECT * FROM CHANGETABLE(CHANGES TestCT, 1) CT
返回結果則會變為

SYS_CHANGE _VERSION	SYS_CHANGE _CREATION_VERSION	SYS_CHANGE _OPERATION	SYS_CHANGE _COLUMNS	SYS_CHANGE _CONTEXT	ID
2	1	U	0x0000000002000000	NULL	1

這次SYS_CHANGE_OPERATION欄位變成了U。原因是我們選取的last_sync_version引數值是1，代表我們上次同步時資料更新版本是1，也就意味著這行資料已經被同步到目的表中了，因此在我們進行同步時只需要處理UPDATE操作。

接著我們嘗試一下
UPDATE TestCT SET ID = 5 WHERE ID = 1

然後我們看看CHANGETABLE會返回什麼
SELECT * FROM CHANGETABLE(CHANGES TestCT, 2) CT
返回結果則會變為

SYS_CHANGE _VERSION	SYS_CHANGE _CREATION_VERSION	SYS_CHANGE _OPERATION	SYS_CHANGE _COLUMNS	SYS_CHANGE _CONTEXT	ID
3	NULL	D	NULL	NULL	1
3	3	I	NULL	NULL	5

返回結果會變成兩條，這是因為我們這次UPDATE修改的是主鍵，所以在表中其實是將原始記錄刪除，然後增加一行新的資料。在同步的時候我們就知道需要刪除先前同步到目的表中的ID為1的記錄，然後複製源表中ID為5的記錄到目的表中。

接下來還可以進行一系列的測試，比如說：

• 在一個事務中修改多條記錄，則會注意到這些記錄在CHANGETABLE表函式返回的結果中擁有的SYS_CHANGE_VERSION是相同的。
• 提交DDL語句，表架構的改變不會在CHANGETABLE表函式的結果中體現，也就是說表結構的改動不被Change Tracking記錄，甚至新增加一個設定了預設值的欄位。不過增加後對新的欄位進行修改就可以被Change Tracking記錄了。這說明了Change Tracking只跟蹤DML，不跟蹤DDL。不過這一點應該不是問題，因為SQL Server 2005開始就有DDL Trigger了，相對DDL語句的提交應該不會過於頻繁，因此通過觸發器的方案來實現對架構的跟蹤應該從成本上是可以接受的。
• 利用WITH CHANGE_TRACKING_CONTEXT (@context)語句在跟蹤記錄中留下DML語句執行上下文的資訊，這一手段通常可用於跟蹤最後一次更新是由哪個應用程式做出的、或由哪個個使用者作出的，通過判斷執行上下文可以在雙向複製中解決衝突的問題。
• 如果執行了TRUNCATE TABLE或者Cleanup程式清理了Change Tracking記錄，那麼可以通過CHANGE_TRACKING_MIN_VALID_VERSION函式了解可獲取的最小更新版本號。如果這個最小版本號比複製目的端記錄的最近一次複製成功的最大版本號都高，則意味著源資料庫已經丟失了一部份尚未複製的記錄，也就代表目的系統需要重新初始化。
• 對於UPDATE語句，如果在啟用表的Change Tracking功能是設定了Track_Column_Updated選項為ON，Change Tracking會記錄下UPDATE語句影響到欄位資訊，這個資訊可以通過使用CHANGE_TRACKING_IS_COLUMN_IN_MASK函式解析，比如說要知道TestCT表的Name欄位是否在版本2的UPDATE操作中被影響到，可以使用CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Name', 'ColumnId'), SYS_CHANGE_COLUMNS)函式。

CHANEGTABLE(VERSION)

這個表函式的語法結構為CHANGETABLE(VERSION table , { ( column_name [ , ...n ] ) , ( value [ , ...n ] ) }，table引數是啟用Change Tracking功能的表名，column_name是主鍵的欄位名，如果主鍵是複合鍵，則需要列舉主鍵中所有的欄位，value是對應前面每個欄位的值。例如上面程式碼，我們可以通過下面語句來得到ID為5的記錄最近的更新版本號是多少：

SELECT * FROM CHANGETABLE(VERSION TestCT, (ID), (5)) CT
返回結果則會變為

SYS_CHANGE _VERSION	SYS_CHANGE _CONTEXT	ID
3	NULL	5

由返回結果可以瞭解到ID為5的資料在當前系統中最後一次更新的更新版本號是3，如果在另外一個系統中ID為5的資料版本號不是3的話，那麼就意味著需要進行復制了。

CHANGETABLE(VERSION)函式與CHANGETABLE(CHANGES)函式最大的區別就在於它可以傳入表的主鍵值，因此可以根據使用者的需求瞭解每一行資料的最新版本號，而CHANGETABLE(CHANGES)則是通過某個版本號來獲得自從這個版本號之後的更新資訊。因此兩個函式可以用於兩種不同的複製拓撲，前者更加適用於雙向複製，而後者則適用於單向複製。

測試指令碼

下面給出完整的測試指令碼：

/*************************************************************
**************************************************************
Section 1: How to enable the change tracking
**************************************************************
**************************************************************/

--Create test database and enable the change tracking feature of the database
USE master
GO
CREATE DATABASE TestCT
GO
ALTER DATABASE TestCT
SET Change_Tracking = ON
(CHANGE_RETENTION = 2 MINUTES, AUTO_CLEANUP = ON)
GO

--Create test table and enable the change tracking feature on the table
USE TestCT
GO
CREATE TABLE TestCT
(
    ID int PRIMARY KEY,
    Name varchar(50),
    Description varchar(200)
)
GO
ALTER TABLE TestCT
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
GO
--Use sys.change_tracking_tables to get the table list which enabled change tracking
SELECT OBJECT_NAME(object_id) AS table_name, sys.change_tracking_tables.* FROM sys.change_tracking_tables
GO

/*************************************************************
**************************************************************
Section 2: How to get change tracking information
**************************************************************
**************************************************************/

--The current version is 0 now.
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
--Insert two rows in two transactions
INSERT INTO TestCT VALUES (1, 'ABC', NULL)
INSERT INTO TestCT VALUES (2, 'XYZ', NULL)
--The current version is 2 now.
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
--Get the changed table version with table function 'CHANGETABLE'.
--You will see two rows with different SYS_CHANGE_VERSION and SYS_CHANGE_CREATION_VERSION.
SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT  

--INSERT two rows in one transaction
BEGIN TRAN
    INSERT INTO TestCT VALUES (3, 'CBA', NULL)
    INSERT INTO TestCT VALUES (4, 'ZYX', NULL)
COMMIT
--The current version is 3 now.
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
--Get the changed table version with table function 'CHANGETABLE'.
--You will see two rows with same SYS_CHANGE_VERSION and SYS_CHANGE_CREATION_VERSION.
--We use 2 as the last_sync_version value here to see net changes of after the last time insertion.
SELECT * FROM CHANGETABLE(CHANGES TestCT, 2) CT

--DML update which not affected the primary key
UPDATE TestCT SET Name = 'abc' WHERE ID = 1
--The current version is 4 now.
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
--Get the changed table version with table function 'CHANGETABLE'.
--With different last_sync_version, you will see the different result
SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT  --the SYS_CHANGE_OPERATION is I now
SELECT * FROM CHANGETABLE(CHANGES TestCT, 3) CT  --the SYS_CHANGE_OPERATION is U now
--With the CHANGE_TRACKING_IS_COLUMN_IN_MASK function, you will see which row changed.
SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Name', 'ColumnId'), SYS_CHANGE_COLUMNS)
    AS is_column_Name_changed,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Description', 'ColumnId'), SYS_CHANGE_COLUMNS)
    AS is_column_Description_changed
    FROM CHANGETABLE(CHANGES TestCT, 3) CT

--DML update which affected the primary key
UPDATE TestCT SET ID = 5 WHERE ID = 1
--The current version is 5 now.
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
--Get the changed table version with table function 'CHANGETABLE'
--you will see one record indicate the deletion of origional record
--and one record indicate the insertion of new record
SELECT * FROM CHANGETABLE(CHANGES TestCT, 4) CT

--DML delete
DELETE TestCT WHERE ID = 5
--The current version is 6 now.
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
--Get the changed table version with table function 'CHANGETABLE'
--you will see one record indicate the deletion of origional record
SELECT * FROM CHANGETABLE(CHANGES TestCT, 5) CT

--Another usefull usage of CHANGETABLE, which can help you
--to determine the current version of a specific row
SELECT * FROM CHANGETABLE(VERSION TestCT, (ID), (4)) CT

--Another usefull statement which can help you distinguish the
--change context
DECLARE @context AS varbinary(128);
SET @context = CAST('Test_Change_Tracking' AS varbinary(128));
WITH CHANGE_TRACKING_CONTEXT (@context)
    UPDATE TestCT SET Description = 'NA';
-- The change now has an associated change context
SELECT CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION,
    CAST(SYS_CHANGE_CONTEXT AS varchar(20)) AS change_context
    FROM CHANGETABLE(CHANGES TestCT, 6) AS CT

--Another usefull function which can help you determine the requirement
--of re-initialization.
--Before the truncation or periodical cleanup, the min_valid_version is 0 now.
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('TestCT')) AS min_valid_version;
--After the truncation or periodical cleanup, the min_valid_version is 7 now.
TRUNCATE TABLE TestCT;
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('TestCT')) AS min_valid_version;
--So the insert statement will use 8 as the SYS_CHANGE_VERSION
INSERT INTO TestCT VALUES (1, 'ABC', NULL)
--So the last_sync_version parameter will not affect the result now
SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT
SELECT * FROM CHANGETABLE(CHANGES TestCT, 7) CT

/*************************************************************
**************************************************************
Section 3: DDL affection to the change tracking
**************************************************************
**************************************************************/

--After the tables schema changed, the new column will also be monitored
ALTER TABLE TestCT ADD Age int;
UPDATE TestCT SET Age = 10;
SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Name', 'ColumnId'), SYS_CHANGE_COLUMNS)
    AS is_column_Name_changed,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Description', 'ColumnId'), SYS_CHANGE_COLUMNS)
    AS is_column_Description_changed,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Age', 'ColumnId'), SYS_CHANGE_COLUMNS)
    AS is_column_Age_changed
    FROM CHANGETABLE(CHANGES TestCT, 8) CT;

--You will find the schema modification will not be tracked.
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
ALTER TABLE TestCT DROP COLUMN Age
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9079672/viewspace-220318/，如需轉載，請註明出處，否則將追究法律責任。