在SQL Server 2008中使用變更跟蹤獲得資料變更

iSQlServer發表於2010-03-21
SQLServer
本篇文章將介紹怎樣獲取變更的資料。使用CHANGETABLE函式，我們可以獲得變更跟蹤資訊。這個函式提供了兩個模式：CHANGES和VERSION。在CHANGES模式中，CHANGETABLE(CHANGES表，last_sync_version)使用所要跟蹤的表的名稱和一個版本數字，並返回這個版本之後對這個表的所有變更。在VERSION模式中，CHANGETABLE(VERSION表，primary_key_column_name [ , ...n ]，(primay_key_column_value [ , ...n ] ))使用一個表名稱、主鍵欄位和主鍵值。主鍵值標識這個表中的一條記錄。VERSION模式返回當前的版本並改變與這個特定記錄關聯的上下文。

　　資料變更需要與一個基線做對比。當你首先將你的客戶端(一個NET應用程式和/或另一個SQL Server表)與HumanResources.Department表同步時，你從這個表的所有記錄獲得一個原始的資料集。你還獲得基線版本數字，例如這時所有記錄的最大版本數字，並將它記錄下來用於下一次同步。下一次同步時，系統確定哪些記錄從這個基線版本以來進行了修改，而一個新的基線版本被儲存下來用於下次的同步。這個演算法如下所示。

　　


-- Obtain the current synchronization version. This will be used
　　the next time CHANGETABLE(CHANGES...) is called.
　　SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();
　　-- If this is the first synchronization session
　　IF (@sync_initialized = 0)
　　BEGIN
　　-- Initialize from the table
　　SELECT DepartmentID, Name, GroupName, ModifiedDate
　　FROM HumanResources.Department
　　END
　　ELSE
　　BEGIN
　　-- Obtain incremental changes by using the synchronization version
　　obtained the last time the data was synchronized.
　　SELECT CT.SYS_CHANGE_OPERATION,
　　DepartmentID, Name, GroupName, ModifiedDate
　　FROM HumanResources.Department
　　RIGHT OUTER JOIN
　　CHANGETABLE(CHANGES HumanResources.Department,
　　@last_synchronization_version) AS CT
　　ON
　　P.DepartmentID = CT.DepartmentID
　　END
　　在我們的例子中，讓我們首先查詢表HumanResources.Department的所有記錄來獲得一個基線並獲得一個基線版本。

　　


DECLARE @synchronization_version bigint
　　-- Obtain the current synchronization version. This will be used
　　the next time CHANGETABLE(CHANGES...) is called.
　　SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
　　SELECT @synchronization_version
　　-- Initialize from the base table
　　SELECT DepartmentID, Name, GroupName, ModifiedDate
　　FROM HumanResources.Department
　　如上所示，基線版本數字是0，因為我們剛剛啟用這個資料庫的變更跟蹤。

　　讓我們插入一條新的記錄到這個表中並更新一條已有的記錄。

INSERT INTO HumanResources.Department

　　(Name, GroupName, ModifiedDate)

　　VALUES ('Product Design', 'Research and Development', GETDATE())

　　UPDATE HumanResources.Department

　　SET GroupName='Accounting'

　　WHERE DepartmentID=10

　　要獲得上次版本0以來的變更，執行下面的指令碼。

　　


DECLARE @synchronization_version bigint
　　-- Obtain the current synchronization version. This will be used the next time CHANGETABLE(CHANGES...) is called.
　　SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
　　SELECT @synchronization_version
　　-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
　　SELECT CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION, CT.DepartmentID, Name, GroupName, ModifiedDate
　　FROM HumanResources.Department D
　　RIGHT OUTER JOIN
　　CHANGETABLE(CHANGES HumanResources.Department, 0) AS CT
　　ON
　　D.DepartmentID = CT.DepartmentID
　　如同你在上面看到的，當前的版本增加到了2。在欄位SYS_CHANGE_OPERATION中的值“U”表示DepartmentID=10的記錄被更新了。在欄位SYS_CHANGE_OPERATION中的值“I”表示DepartmentID=17的記錄被插入。

　　如果你在表HumanResources.Department 上啟用了欄位跟蹤，那麼你可以只從變更了的欄位獲取資料。

　


　DECLARE @synchronization_version bigint
　　-- Obtain the current synchronization version. This will be used the next time CHANGETABLE(CHANGES...) is called.
　　SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
　　SELECT @synchronization_version
　　-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
　　SELECT CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION, CT.DepartmentID,
　　CASE CHANGE_TRACKING_IS_COLUMN_IN_MASK
　　(COLUMNPROPERTY(OBJECT_ID('HumanResources.Department'), 'Name', 'ColumnId'), SYS_CHANGE_COLUMNS)
　　WHEN 1 THEN Name ELSE NULL END as Name,
　　CASE CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY
　　(OBJECT_ID('HumanResources.Department'), 'GroupName', 'ColumnId'), SYS_CHANGE_COLUMNS)
　　WHEN 1 THEN GroupName ELSE NULL END AS GroupName,
　　ModifiedDate
　　FROM HumanResources.Department D
　　RIGHT OUTER JOIN
　　CHANGETABLE(CHANGES HumanResources.Department, 0) AS CT
　　ON
　　D.DepartmentID = CT.DepartmentID
　　如同你在上圖中看到的，第一條記錄Name欄位的值沒有返回來，這是因為它在更新語句中沒有被更新。在這個例子中，Name欄位只是一個nvarchar欄位，它不佔什麼儲存量。這裡只用這個欄位來做個說明。LOB欄位用於欄位跟蹤更好一些，因為啟用欄位跟蹤的成本與通過網路獲得LOB資料的好處相比要小很多。

　讓我們刪除新新增的記錄。

　


　DELETE FROM HumanResources.Department
　　WHERE DepartmentID=17
　　要獲得從上次版本2以來的變更，執行之前的指令碼，使用新版本2。
　　DECLARE @synchronization_version bigint
　　-- Obtain the current synchronization version. This will be used the next time CHANGETABLE(CHANGES...) is called.
　　SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
　　SELECT @synchronization_version
　　-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
　　SELECT CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION, CT.DepartmentID, Name, GroupName, ModifiedDate
　　FROM HumanResources.Department D
　　RIGHT OUTER JOIN
　　CHANGETABLE(CHANGES HumanResources.Department, 2) AS CT
　　ON
　　D.DepartmentID = CT.DepartmentID
　　如上圖所示，SYS_CHANGE_OPERATION欄位中的值“D”表示DepartmentID=17的記錄被刪除了。

　　因為如果AUTO_CLEANUP選項設定為開啟，那麼變更跟蹤資訊可以在下次同步執行之前被刪除，我們還需要將@last_synchronization_version和用於特定表的變更跟蹤表中儲存的最小版本相比較。如果@last_synchronization_version小於CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('HumanResources.Department'))，那麼我們需要重新初始化並獲得一個包含HumanResources.Department base表所有記錄的新基線。因此，這個條件

　　IF (@sync_initialized = 0)

　　需要改為

　　IF (@last_synchronization_version

　　在一個繁忙的系統上，在獲得當前版本和從CHANGETABLE()函式獲得變更之間，表HumanResources.Department 有可能被其它會話改變，或變更跟蹤資訊可能被清理程式刪除了並啟動CHANGE_TRACKING_MIN_VALID_VERSION來新增。因此，最好使用快照隔離來確保在事務過程中所有的變更跟蹤資訊是一致的。完整的演算法如下所示。

　　


SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
　　BEGIN TRAN
　　DECLARE @synchronization_version bigint
　　-- Obtain the current synchronization version. This will be used the next time CHANGETABLE(CHANGES...) is called.
　　SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
　　SELECT @synchronization_version
　　IF (@last_synchronization_version 　　BEGIN
　　-- Initialize from the base table
　　SELECT DepartmentID, Name, GroupName, ModifiedDate
　　FROM HumanResources.Department
　　END
　　ELSE
　　BEGIN
　　-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
　　SELECT CT.SYS_CHANGE_OPERATION, CT.DepartmentID, Name, GroupName, ModifiedDate
　　FROM HumanResources.Department D
　　RIGHT OUTER JOIN
　　CHANGETABLE(CHANGES HumanResources.Department, @last_synchronization_version) AS CT
　　ON
　　D.DepartmentID = CT.DepartmentID
　　END
　　COMMIT TRAN
　　在資料庫上啟用快照隔離會對資料庫增加不小的效能成本。如果你傾向於不使用快照隔離，那麼一個獲得變更的替代演算法如下所示。

　　


DECLARE @synchronization_version bigint
　　SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
　　SELECT @synchronization_version
　　IF (@last_synchronization_version 　　-- Initialize from the base table
　　SELECT DepartmentID, Name, GroupName, ModifiedDate
　　FROM HumanResources.Department
　　ELSE
　　BEGIN
　　-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
　　SELECT CT.SYS_CHANGE_OPERATION, CT.DepartmentID, Name, GroupName, ModifiedDate
　　FROM HumanResources.Department D
　　RIGHT OUTER JOIN
　　CHANGETABLE(CHANGES HumanResources.Department, @last_synchronization_version) AS CT
　　ON
　　D.DepartmentID = CT.DepartmentID
　　WHERE (CT.SYS_CHANGE_CREATION_VERSION <= @synchronization_version)
　　END
　　這個演算法不使用快照隔離，它使用新版本來確保這個新版本從CHANGETABLE(CHANGES …)函式返回之後沒有變更。但是，當CHANGE_TRACKING_MIN_VALID_VERSION改變而@last_synchronization_version變得無效時，這個演算法就不能解決這個問題。

　　總結

　　本篇文章介紹了怎樣使用CHANGETABLE函式來獲取資料變更。展示了兩個演算法。你可以在你的.NET應用程式中使用Sync Services來執行這兩個演算法。

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

相關文章