在SQL Server 2008中使用变更跟踪获得数据变更。

 

　　本篇文章将介绍怎样获取变更的数据。使用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 < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('HumanResources.Department')))
　　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 < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('HumanResources.Department')))
　　-- 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来执行这两个算法。