前段时间评估了一下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是一个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
返回结果为

从返回结果看，主键值为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_OPERATION字段变成了U。原因是我们选取的last_sync_version参数值是1，代表我们上次同步时数据更新版本是1，也就意味着这行数据已经被同步到目的表中了，因此在我们进行同步时只需要处理UPDATE操作。

接着我们尝试一下
UPDATE TestCT SET ID = 5 WHERE ID = 1

然后我们看看CHANGETABLE会返回什么
SELECT * FROM CHANGETABLE(CHANGES TestCT, 2) CT
返回结果则会变为

返回结果会变成两条，这是因为我们这次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
返回结果则会变为

由返回结果可以了解到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