SQL Server 2008新特性——變更追蹤

標籤：

在大型的資料庫應用中，經常會遇到部分資料的離線和多個資料庫的合并問題。比如現在有一個全省範圍使用的應用程式，每個市都部署了單獨的相同的應用程式伺服器和資料庫伺服器，每個月需要將全省所有市的資料全部匯總起來用於出全省的報表，這是一種很常見的資料庫合并問題。再比如我們做了一個SmartClient的應用程式，每個用戶端都有應用程式和資料庫，另外還有一個中樞資料庫用於匯總所有用戶端的資料。每個智能用戶端上都可以對自己的資料庫進行增刪改查，一旦智能用戶端串連到網路上時，系統就將用戶端資料庫中的資料更改全部應用到中樞資料庫中，這種偶爾連線應用程式程式也是需要資料庫的同步的。

對於前面說到的這些應用，最簡單的同步方法就是刪除原有資料，然後重新填充新的資料，對於小資料量的表來說這並沒有什麼問題，但是如果每個市都有幾百萬幾千萬條資料，那麼要將省資料庫中的資料刪除了再把每個市中的資料全部填充到省資料庫中顯然是不可行的。這種情況下應該使用跟蹤資料更改的方法，將每個市這個月的資料更改應用到省資料庫中（感覺有點像是差異備份一樣，只記錄更改的）。在SQL Server 2008中提供了兩種跟蹤資料更改的方案：

• 變更資料擷取（Change Data Capture）
• 變更追蹤（Chang Tracking）

今天我主要說的是變更追蹤，變更資料擷取在以後進行講解。

啟用變更追蹤

變更追蹤是SQL Server 2008的一個新特性，預設情況下是沒啟用的。變更追蹤可以應用跟蹤到具體一個資料庫中的具體表甚至是具體的列。變更追蹤並不會建立觸發器之類的對象，只是在使用者對啟用了變更追蹤的表進行了增加、修改和刪除操作時，系統自動將該操作產生一個版本號碼，記錄下操作的時間戳記、操作的類型、受影響的資料的主鍵等資訊。啟用變更追蹤後對資料操作的效能影響不是很大。這些資訊是記錄到SQL Server系統資料表中的，系統自動負責清理和維護。

要使用變更追蹤需要啟用資料庫的變更追蹤功能和表的變更追蹤功能。在SSMS中資料庫的屬性視窗中可以啟用資料庫的變更追蹤：

這裡將變更追蹤選項設定為true既可啟用變更追蹤。另外3個選項就是跟蹤的資料自動清理的開關和清理的時間，這個自動清理的時間必須大於我們要同步資料的周期，比如我們的資料是一個月同步一次，那麼這個保持期就應該大於31天，如果設定保持期太短，那麼我們的跟蹤資料還沒來得及同步就被自動清理了。

這裡只是啟用了資料庫的變更追蹤，接下來是要啟用表的變更追蹤。這裡我們建立一個新的表t1並初始化幾條資料：

 

CREATE TABLE t1 
( 
    c1 INT IDENTITY PRIMARY KEY, 
    c2 VARCHAR(50) NOT NULL, 
    c3 DATETIME NOT NULL, 
    c4 VARCHAR(max) 
) 
GO 
INSERT INTO t1 VALUES ( ‘test1‘,‘2009-1-1‘,‘www.cnblogs.com/studyzy‘ ) 
INSERT INTO t1 VALUES ( ‘test2‘,‘2009-1-1‘,‘www.cnblogs.com/studyzy‘ ) 
INSERT INTO t1 VALUES ( ‘test3‘,‘2009-1-2‘,‘www.cnblogs.com/studyzy‘ ) 

接下來在SSMS中查看錶t1的屬性視窗，可以在屬性視窗中啟用該表的變更追蹤功能：

其中第二個選項“跟蹤已更新的列”是表示是否將變更追蹤細化到列上。對於一般的表來說，我們只需要知道具體哪些行進行了更改，然後在合并資料時將整行資料更新到中樞資料庫既可，但是如果表中有大對象列（text image varchar(max) varbinary(max) xml等資料類型的列）時，將整行進行更新可能非常慢，所以我們可以啟用“跟蹤已更新的列”將具體更新了哪些列記錄下來，這樣在合并資料時就直接更新這些列既可。

變更追蹤常用函數

在變更追蹤中最重要的一點就是版本號碼，版本號碼從0開始一直遞增，對錶的每一次更改操作都會產生一個新的版本號碼。使用

SELECT CHANGE_TRACKING_MIN_VALID_VERSION( OBJECT_ID(‘dbo.t1‘))

可以獲得t1表最小版本號碼，由於是剛建立變更追蹤，所以這裡返回的是0，如果我們進行了大量的操作以後，而且這些操作的時間已經超過了資料庫變更追蹤中設定的保持期時間，那麼到期的版本就會被系統自動清理，清理後最小版本就不是0了，而是保留的可用的最早版本。

SELECT CHANGE_TRACKING_CURRENT_VERSION()可以獲得當前資料庫的變更追蹤的最新版本。這裡由於我們啟用變更追蹤後還沒有進行資料庫操作，所以返回的也是0。

現在我們向表t1中插入一條資料，然後查看當前最新版本：

INSERT INTO t1 VALUES ( ‘test‘,‘2009-1-4‘,‘www.cnblogs.com/studyzy‘ ) 
SELECT CHANGE_TRACKING_CURRENT_VERSION() --返回1

現在返回的版本號碼就是1了。

接下來我們再修改2條資料和刪除1條資料，再查看版本號碼：

UPDATE t1 SET c3=GETDATE() WHERE c1<3 --受影響2條資料 
DELETE FROM t1 WHERE c2=‘test3‘ --受影響1條資料 
SELECT CHANGE_TRACKING_CURRENT_VERSION() --返回3

這裡我們總共影響了4條資料，但是版本號碼為3說明版本號碼並不是以受影響的行實來定的，一次更新操作中不管影響了好多條資料（當然這裡不能為0條）版本號碼只增加1。

現在版本號碼有了，接下來就是查詢出這段時間t1的更改情況，需要使用資料表值函式：CHANGETABLE(CHANGES [要查詢變更追蹤的表名], 從哪個版本開始的更改)。這裡要查詢t1表從0版本開始到現在的所有資料更改，那麼對應的查詢語句是：

SELECT * 
FROM CHANGETABLE(CHANGES dbo.t1,0) as ct

 

系統返回結果：

SYS_CHANGE_VERSION	SYS_CHANGE_CREATION_VERSION	SYS_CHANGE_OPERATION	SYS_CHANGE_COLUMNS	SYS_CHANGE_CONTEXT	c1
2	NULL	U	0x0000000003000000	NULL	1
2	NULL	U	0x0000000003000000	NULL	2
3	NULL	D	NULL	NULL	3
1	1	I	NULL	NULL	4

這裡每個列的資料類型、含義等在聯機叢書裡面解釋的很清楚，我這裡只簡單介紹下返回的這個表：

在版本號碼為1的資料更改操作中是插入了一條資料，插入資料的主鍵c1=4；在版本號碼2的操作中更新了2條資料，分別是c1=1和c1=2的行；在版本3的操作中刪除了c1=3的一條資料。

根據變更追蹤同步資料

現在所有的更改已經查詢出來了，接下來就可以根據查詢出來的這個結果同步資料了。為了示範方便，我這裡將在同一個執行個體中建立TestDB1資料庫並初始化t1表用於表示中樞資料庫。那麼同步資料的操作應該是：

 

--首先將新增的資料插入到中樞資料庫中：
SET IDENTITY_INSERT TestDB1.dbo.t1 ON 
INSERT INTO TestDB1.dbo.t1(c1,c2,c3,c4) 
SELECT t1.* 
FROM CHANGETABLE(CHANGES dbo.t1,0) AS ct 
INNER JOIN t1 
ON ct.c1=t1.c1 
WHERE ct.SYS_CHANGE_OPERATION=‘I‘ 

--接下來將更改的資料應用到中樞資料庫中：
UPDATE TestDB1.dbo.t1 
SET c2=newt1.c2,c3=newt1.c3,c4=newt1.c4 
FROM CHANGETABLE(CHANGES dbo.t1,0) AS ct 
INNER JOIN dbo.t1 AS newt1 
ON ct.c1=newt1.c1 
WHERE ct.SYS_CHANGE_OPERATION=‘U‘ AND t1.c1=newt1.c1 

--將刪除的資料從中樞資料庫刪除：
DELETE FROM  TestDB1.dbo.t1 
WHERE c1 IN ( 
            SELECT c1 
            FROM CHANGETABLE(CHANGES dbo.t1,0) AS ct 
            WHERE ct.SYS_CHANGE_OPERATION=‘D‘)

這樣我們就使用變更追蹤實現了資料庫的同步。該同步操作時的版本號碼是3，這個版本號碼必須要單獨記下來，那麼下次再進行同步是就從3開始查詢。

通過變更追蹤更新列

前面的同步指令碼中關於資料update操作是：

UPDATE TestDB1.dbo.t1 
SET c2=newt1.c2,c3=newt1.c3,c4=newt1.c4

由於c4是大對象資料類型，如果裡面存放了幾十兆或者更大的資料，而實際上我們更新的並不是c4列，那麼這種更新方式必然很浪費時間和資源。前面我們對t1表已經啟用了“跟蹤已更新的列”，那麼就可以根據實際更新的列來更新資料。

使用CHANGE_TRACKING_IS_COLUMN_IN_MASK()函數可以判斷一個列是否發生了更改，如果發生了更改則返回1，沒有更改則返回0。比如查詢c2是否發生更改：

SELECT * ,CHANGE_TRACKING_IS_COLUMN_IN_MASK ( COLUMNPROPERTY( OBJECT_ID(‘dbo.t1‘),‘c2‘,‘ColumnId‘) , SYS_CHANGE_COLUMNS ) 
FROM CHANGETABLE(CHANGES dbo.t1,0) AS ct 
WHERE ct.SYS_CHANGE_OPERATION=‘U‘

這裡返回0說明沒有更改c2列，同樣的方法可以判斷出c3列發生了更改。

既然可以判斷哪些列發生了更改，那麼就可以根據發生更改的列來更新該列的資料，比如對於c2的更新語句就是：

UPDATE TestDB1.dbo.t1 
SET c2=newt1.c2 --更新c2列 
FROM CHANGETABLE(CHANGES dbo.t1,0) AS ct 
INNER JOIN dbo.t1 AS newt1 
ON ct.c1=newt1.c1 
WHERE ct.SYS_CHANGE_OPERATION=‘U‘ AND t1.c1=newt1.c1 
AND CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(‘dbo.t1‘),‘c2‘,‘ColumnId‘) , ct.SYS_CHANGE_COLUMNS )=1 --發生更改時才更新

SQL Server 2008新特性——變更追蹤