初探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功能，可以通过提交如下语句ITPUB个人空间l)Z8T1U,i@6U?-\
   ALTER DATABASE TestCTITPUB个人空间#t[#M L S+rC1a W
   SET CHANGE_TRACKING = ONITPUB个人空间w$^2Q8GW,R-Oe
   (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表上的更新跟踪，我们可以提交如下语句ITPUB个人空间u \4NO2IO&p
   ALTER TABLE TestCT
   ;Dn#wy5Y2zh9a0ENABLE CHANGE_TRACKINGITPUB个人空间0tYjT;}'YN%\3y"l
   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(CHANGEStable,last_sync_version)，table参数为激活Change Tracking功能的表名，last_sync_version参数是希望获取更新的最小版本号。

table参数的值非常明白，我们希望获得哪张表的更新信息，就是这张表的名字了，而last_sync_version是影响返回结果集的主要因素。
+or-dX9\`4}E0举个例子，我们用下面这段脚本来详细说明Change Tracking记录下的信息：

CREATE TABLE TestCT
)W~)Rn^/Il` @L,iw"]0(
}'nv$e
T*j"na0    ID int PRIMARY KEY,
bve C+qZr0    Name varchar(50),ITPUB个人空间 E)f*Q$qU@M
    Description varchar(200)
|S`$_ cV0yq9H0)
#d{h8CNy.V&w;@0GO
2t[y.r6}$@{!V0ALTER TABLE TestCT
,H;Sm'v9R`0ENABLE CHANGE_TRACKING
#@6\8ON*H0WITH (TRACK_COLUMNS_UPDATED = ON)ITPUB个人空间6U2D'Ii$P+k0{*`L
GO

这个时候TestCT表的Change Tracking功能就被激活了，这张表上所有的数据更新都会被记录到一张内部表中，接着我们就可以在TestCT表上提交各种DML语句了。ITPUB个人空间]
O,}?0X,mz$bS M[
INSERT INTO TestCT VALUES (1, 'ABC', NULL)

这个时候我们可以通过CHANGETABLE函数来查看TestCT的更新历史ITPUB个人空间1mz:a"?0I$e
SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT
9iy `,@#Q?0返回结果为ITPUB个人空间,Y},rvrJ9sJ

返回结果集中：

• 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字段。

我们接着测试，
}dai,`B @tb0|
p0UPDATE TestCT SET Name = 'abc' WHERE ID = 1

提交完这句语句后，其实TestCT表已经有了两次更新，一次是INSERT操作，一次是UPDATE操作，但是这两次操作都是针对ID为1的这一行数据，这个时候我们在CHANGETABLE函数中通过不同的last_sync_version参数会得到不同的返回结果。

SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT
t1V8DXk4^0返回结果为
'X.R/Zs0`%r5z0

从返回结果看，主键值为1的这行数据最近的更新版本为2，而插入表时的更新版本为1，代表这行数据在插入数据表后已经有过更新，但是SYS_CHANGE_OPERATION仍然是I。原因是我们选取的last_sync_version参数值是0，代表我们上次同步时数据更新版本是0，而这行数据是在版本0后被插入表的，因此这行数据还没有出现在同步的目的表中，因此在我们进行同步时首先要处理INSERT操作，而被INSERT到源表中的原始记录已经被更新了(在版本2时)，因此我们只能将INSERT操作和UPDATE操作进行合并，直接取得UPDATE后的数据行写入目的表。

但是如果我们提交的语句是
5@:R|4|m/S0SELECT * FROM CHANGETABLE(CHANGES TestCT, 1) CT
$V&G W3Z,FnXC#c9^0返回结果则会变为ITPUB个人空间 b5` T2Z7{1_9@*\_0z

这次SYS_CHANGE_OPERATION字段变成了U。原因是我们选取的last_sync_version参数值是1，代表我们上次同步时数据更新版本是1，也就意味着这行数据已经被同步到目的表中了，因此在我们进行同步时只需要处理UPDATE操作。

接着我们尝试一下ITPUB个人空间b@!eu^
UPDATE TestCT SET ID = 5 WHERE ID = 1

然后我们看看CHANGETABLE会返回什么ITPUB个人空间9D.u Fh8Ln9R
SELECT * FROM CHANGETABLE(CHANGES TestCT, 2) CT
+m].A4s.h:FC0返回结果则会变为ITPUB个人空间+nl,V!bSw

返回结果会变成两条，这是因为我们这次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(VERSIONtable, { (column_name[ , ...n] ) , (value[ , ...n] ) }，table参数是激活Change Tracking功能的表名，column_name是主键的字段名，如果主键是复合键，则需要列举主键中所有的字段，value是对应前面每个字段的值。例如上面代码，我们可以通过下面语句来得到ID为5的记录最近的更新版本号是多少：

SELECT * FROM CHANGETABLE(VERSION TestCT, (ID), (5)) CT
?$eJ`!Ly|W+t ]0返回结果则会变为ITPUB个人空间`I:G D
r Q.f!`YGo

由返回结果可以了解到ID为5的数据在当前系统中最后一次更新的更新版本号是3，如果在另外一个系统中ID为5的数据版本号不是3的话，那么就意味着需要进行复制了。

CHANGETABLE(VERSION)函数与CHANGETABLE(CHANGES)函数最大的区别就在于它可以传入表的主键值，因此可以根据用户的需求了解每一行数据的最新版本号，而CHANGETABLE(CHANGES)则是通过某个版本号来获得自从这个版本号之后的更新信息。因此两个函数可以用于两种不同的复制拓扑，前者更加适用于双向复制，而后者则适用于单向复制。

测试脚本

下面给出完整的测试脚本：

/*************************************************************ITPUB个人空间:gi*ma/N I
**************************************************************
2K:tt.~Ia0Section 1: How to enable the change tracking
HHk$hY:q d0**************************************************************ITPUB个人空间a!Yw]%kEG
**************************************************************/

--Create test database and enable the change tracking feature of the databaseITPUB个人空间.uFB3K7{5l[4?z
USE master
{-K$z#l r R8Ys0GOITPUB个人空间3~nU&SNV
CREATE DATABASE TestCTITPUB个人空间,y j(A z7{5nWZ2k
GO
:?0b8k1r.t!~l/k1P0ALTER DATABASE TestCTITPUB个人空间
FMbfH5Uu1A
SET Change_Tracking = ON
*i$_0_}g F0(CHANGE_RETENTION = 2 MINUTES, AUTO_CLEANUP = ON)ITPUB个人空间/JzXkp9\fO
GO

--Create test table and enable the change tracking feature on the tableITPUB个人空间K"AD8I;e[)yM4q
USE TestCTITPUB个人空间&mOzw
z3MR
GOITPUB个人空间U3Ge3@5Bo8i3{H
CREATE TABLE TestCTITPUB个人空间t5ECZ/r3w&Mo1K&P B?{
(
-pr`'p.X)~R0    ID int PRIMARY KEY,
M(B9`%t\])ug0    Name varchar(50),ITPUB个人空间oBz/Y6q
    Description varchar(200)ITPUB个人空间*~S%z F.^ P.ud4r o
)ITPUB个人空间 c
o;N6yjdI%}\.`M
GO
SL.J%Nea}i.n0ALTER TABLE TestCT
mZQ'K-GG,s"q0ENABLE CHANGE_TRACKING
8t*F;@ r?"D!D-Q0WITH (TRACK_COLUMNS_UPDATED = ON)ITPUB个人空间#?]b3L?
GOITPUB个人空间9Vq)v8m/NY Z?
--Use sys.change_tracking_tables to get the table list which enabled change trackingITPUB个人空间5b!a,xL Xx/S/w*t0] ~
SELECT OBJECT_NAME(object_id) AS table_name, sys.change_tracking_tables.* FROM sys.change_tracking_tablesITPUB个人空间.KJ4Q wr#o
GO

/*************************************************************
;m&~Al ^8c~!I*[ C0**************************************************************
'B.q5u:E$Zl$JL'X4|0Section 2: How to get change tracking informationITPUB个人空间,QAB6hN
**************************************************************ITPUB个人空间2O"i$Y[w5v
**************************************************************/

--The current version is 0 now.
+Y"W]
VF2q8Aq0SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version

K6[/c |Oh7GBG0--Insert two rows in two transactions
&k0rf3PH0INSERT INTO TestCT VALUES (1, 'ABC', NULL)
$u)lp+J&aO2Y&Q([:rQ0INSERT INTO TestCT VALUES (2, 'XYZ', NULL)
'}*U,d.G2rgh.K0--The current version is 2 now.
Xd&F1S0wKP-`&C,`9Q0SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_versionITPUB个人空间/G0i3MRx%R]
--Get the changed table version with table function 'CHANGETABLE'.
F5?^ LH_^)V0--You will see two rows with different SYS_CHANGE_VERSION and SYS_CHANGE_CREATION_VERSION.
RN4B,B+y?([0SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT  

--INSERT two rows in one transaction
@n$?*R9w#c&eB${0BEGIN TRAN
(Y/EB*ic%Q0    INSERT INTO TestCT VALUES (3, 'CBA', NULL)ITPUB个人空间 ?*Grx5F
    INSERT INTO TestCT VALUES (4, 'ZYX', NULL)ITPUB个人空间Ec5ziM/WJ/b"?
COMMITITPUB个人空间L'A&V GMD8j*]
--The current version is 3 now.
%`7M|6Bdf K[0SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_versionITPUB个人空间GN2|6b9`*K1hg
--Get the changed table version with table function 'CHANGETABLE'.ITPUB个人空间3f*G6?-P+[c0g*z0]
--You will see two rows with same SYS_CHANGE_VERSION and SYS_CHANGE_CREATION_VERSION.ITPUB个人空间!O/_/~d&Ns
--We use 2 as the last_sync_version value here to see net changes of after the last time insertion.
Koi)w
QrZ'c0SELECT * FROM CHANGETABLE(CHANGES TestCT, 2) CT

--DML update which not affected the primary keyITPUB个人空间{:|O;IO6]SO0Kf
UPDATE TestCT SET Name = 'abc' WHERE ID = 1
k-y'N4l2c/p0--The current version is 4 now.ITPUB个人空间.|Nm7Wx(VQJ;D8|Y
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
*J1?/n)kP N#C/v0--Get the changed table version with table function 'CHANGETABLE'.ITPUB个人空间"]Y,m2E-^U/r
--With different last_sync_version, you will see the different result
BI&P0_ J#J0SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT  --the SYS_CHANGE_OPERATION is I now
7[ s(B$?D[h,Y0SELECT * FROM CHANGETABLE(CHANGES TestCT, 3) CT  --the SYS_CHANGE_OPERATION is U now
8N#{3ertQqs0--With the CHANGE_TRACKING_IS_COLUMN_IN_MASK function, you will see which row changed.ITPUB个人空间7XHUb.Zdq_W
SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK
Hq:u3\?$q;p0    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Name', 'ColumnId'), SYS_CHANGE_COLUMNS)
anj(L&{R0    AS is_column_Name_changed,
M.RXU&z,r^:X3c!k0    CHANGE_TRACKING_IS_COLUMN_IN_MASK
0z
eC4[)e.h'N0    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Description', 'ColumnId'), SYS_CHANGE_COLUMNS)

`a(t'EE,h0    AS is_column_Description_changed
FV%I O QC1h;k"G0    FROM CHANGETABLE(CHANGES TestCT, 3) CT

--DML update which affected the primary keyITPUB个人空间c"|.?g(lxy
UPDATE TestCT SET ID = 5 WHERE ID = 1ITPUB个人空间m,bj3i0f/S!g
--The current version is 5 now.ITPUB个人空间*Y\p\~:e9W
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
e,w*|WYAc0--Get the changed table version with table function 'CHANGETABLE'ITPUB个人空间$VO$j[$HtI7L
--you will see one record indicate the deletion of origional recordITPUB个人空间/Q
Ed5]3A0c6T
--and one record indicate the insertion of new recordITPUB个人空间-@N.G4R@ M)B
SELECT * FROM CHANGETABLE(CHANGES TestCT, 4) CT

--DML deleteITPUB个人空间3k8S%m0m$C2K^/i
DELETE TestCT WHERE ID = 5ITPUB个人空间Mt w}Bt
--The current version is 6 now.ITPUB个人空间s2_'jft
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_versionITPUB个人空间-AGV8nd*W X%w e
--Get the changed table version with table function 'CHANGETABLE'ITPUB个人空间T8{1]!X0k
--you will see one record indicate the deletion of origional record
1[3kFP WU2U?M0SELECT * FROM CHANGETABLE(CHANGES TestCT, 5) CT

--Another usefull usage of CHANGETABLE, which can help you
p-wAV.Zaa0--to determine the current version of a specific row
t&JK/~UhaX w F0SELECT * FROM CHANGETABLE(VERSION TestCT, (ID), (4)) CT

--Another usefull statement which can help you distinguish the
}2om L2U0--change context
AHv/N;l l"E b9\j3j0DECLARE @context AS varbinary(128);ITPUB个人空间-X+C\jCRz%yS
SET @context = CAST('Test_Change_Tracking' AS varbinary(128));ITPUB个人空间%PH}FUS
WITH CHANGE_TRACKING_CONTEXT (@context)
k5B;^X,p8D7xw7J0    UPDATE TestCT SET Description = 'NA';ITPUB个人空间| ?qEmD
-- The change now has an associated change contextITPUB个人空间#btc^7]1z0Gv
SELECT CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION,
h5E F Ar-n2R0    CAST(SYS_CHANGE_CONTEXT AS varchar(20)) AS change_context
e-^0D/kspx0    FROM CHANGETABLE(CHANGES TestCT, 6) AS CT

--Another usefull function which can help you determine the requirement
nz&V G;H"r X0--of re-initialization.
"x0px(|R0--Before the truncation or periodical cleanup, the min_valid_version is 0 now.
0B-S KN6wS$W0SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('TestCT')) AS min_valid_version;ITPUB个人空间+u"h'f:z }{
--After the truncation or periodical cleanup, the min_valid_version is 7 now.
Nz"T [qes+I0TRUNCATE TABLE TestCT;ITPUB个人空间m8Fzg,A%@T_
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('TestCT')) AS min_valid_version;
5c(j9N@+Wqb/b3\;Z!h8c0--So the insert statement will use 8 as the SYS_CHANGE_VERSION
Zjj:g3i4`-nr0INSERT INTO TestCT VALUES (1, 'ABC', NULL)
3i4HIE r+Q:I0--So the last_sync_version parameter will not affect the result nowITPUB个人空间"tYug0RF
SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CTITPUB个人空间I$Qg"?/udH#[y\1@
SELECT * FROM CHANGETABLE(CHANGES TestCT, 7) CT

/*************************************************************
2PTK3x"ZnUy0**************************************************************
q&S`
tC3{4gV2J0Section 3: DDL affection to the change tracking
6d&Do.Ki(A%iZ0**************************************************************ITPUB个人空间|4N`4it*{a
**************************************************************/

--After the tables schema changed, the new column will also be monitored
iO(~0CS0ALTER TABLE TestCT ADD Age int;
*K
\O$x3~0UPDATE TestCT SET Age = 10;ITPUB个人空间*|nwVrE
i%]/}b}8h
SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK
R(ocG c0    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Name', 'ColumnId'), SYS_CHANGE_COLUMNS)ITPUB个人空间5H&R3iDN
i2CJ
    AS is_column_Name_changed,
]:z_.B#L!?0    CHANGE_TRACKING_IS_COLUMN_IN_MASK
#f:F5j0y)AV#X[0    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Description', 'ColumnId'), SYS_CHANGE_COLUMNS)ITPUB个人空间m;Cl4\9\e
    AS is_column_Description_changed,ITPUB个人空间b_ Y)hSm2]q)B
    CHANGE_TRACKING_IS_COLUMN_IN_MASK
){T0A5ci7}`q0    (COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Age', 'ColumnId'), SYS_CHANGE_COLUMNS)
0G
z*v5^\H.uN$h0    AS is_column_Age_changedITPUB个人空间Z!Fyk Q
    FROM CHANGETABLE(CHANGES TestCT, 8) CT;

--You will find the schema modification will not be tracked.ITPUB个人空间-X.L9t)M(l$Sz\e$M
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version
R3RzgTYa @0ALTER TABLE TestCT DROP COLUMN Age
G|(C&JFI
~
B |0SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version