[一步一步] SQL Server 2008 更改跟踪

更改跟踪介绍：

更改跟踪捕获表的数据行更改这一行为，但不会捕获更改的具体数据。捕获的结果包含表的主键及相关的跟踪信息（例如更改的操作类型、更新操作影响的列等）。

应用程序可以利用这个捕获的结果来确定表的最新更新，并可以关联原始来来获取最新的数据。

[一步一步] SQL Server 2008 更改跟踪：

Step1：创建测试数据库 DB_Test

--

USE Master;

GO

CREATE

DATABASE DB_test;

GO

ALTER

DATABASE DB_test SET

CHANGE_TRACKING=ON(

AUTO_CLEANUP=ON,          -- 

CHANGE_RETENTION= 1 HOURS  -- 1 

);

GO

观察[数据库属性]:

Step2：创建测试表 testTable

USE DB_test;

GO

CREATE

TABLE dbo.testTable(

    id  int CONSTRAINT PK_testTable_id PRIMARYKEY,

    col1  int,

    col2  varchar(10),

    col3  nvarchar(max),

    col4  varbinary(max),

    col5 xml

);

GO

ALTER TABLE dbo.testTable

ENABLECHANGE_TRACKING

WITH(

TRACK_COLUMNS_UPDATED=ON  -- UPDATE 

);

GO

观察[表属性]: 

Step3：数据插入之 测试

Step3.1：先插入3条数据

INSERT dbo.testTable(

    id, col1, col2, col3, col4, col5)

VALUES

(    1,   1 ,'AA','AAA', 0x1,'<a>aa</a>'),

(    2,   2 ,'BB','BBB', 0x2,'<b/>'),

(    3,3 ,'CC','CCC', 0x2,'<c/>');

观察[查询结果]:

SELECT  CHANGE_TRACKING_CURRENT_VERSION()as CHANGETRACKING_CURRENTVERSION ,

CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.testTable'))as CHANGETRACKING_MINVALIDVERSION, *

FROM

CHANGETABLE(CHANGES dbo.testTable, 0) CHG

LEFTJOIN dbo.testTable DATA

ON DATA.id = CHG.id; 

Step3.2：再插入3条数据

INSERT dbo.testTable(id,col1, col2, col3, col4, col5)

VALUES (4,4,'DD','DDD', 0x1,'<d>dd</d>'),

(5,5,'EE','EEE', 0x2,'<e/>'),

(6,6,'FF','FFF', 0x2,'<f/>');

观察[查询结果]:

SELECT  CHANGE_TRACKING_CURRENT_VERSION()as CHANGETRACKING_CURRENTVERSION ,

CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.testTable'))as CHANGETRACKING_MINVALIDVERSION, *

FROM

CHANGETABLE(CHANGES dbo.testTable, 0) CHG

LEFTJOIN dbo.testTable DATA

ON DATA.id = CHG.id; 

Step4：数据更新之 测试

Step4.1: 通过简单语句更新Int类型列值

  UPDATE dbo.testTable SET col1= 111

  WHERE id = 1;

  观察[查询结果]:

　SELECT  CHANGE_TRACKING_CURRENT_VERSION()as CHANGETRACKING_CURRENTVERSION ,

   　　CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.testTable'))as CHANGETRACKING_MINVALIDVERSION, *

　FROM

　　CHANGETABLE(CHANGES dbo.testTable, 0) CHG

　LEFTJOIN dbo.testTable DATA

　ON DATA.id = CHG.id; 

  Step4.2: 通过事务连续更新Int类型列值2次

BEGIN TRAN;

UPDATE dbo.testTable SET col1= 22

WHERE id = 2;

UPDATE dbo.testTable SET col1 = 222

WHERE id = 2;

COMMIT TRAN;

  观察[查询结果]:

　SELECT  CHANGE_TRACKING_CURRENT_VERSION()as CHANGETRACKING_CURRENTVERSION ,

   　　CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.testTable'))as CHANGETRACKING_MINVALIDVERSION, *

　FROM

　　CHANGETABLE(CHANGES dbo.testTable, 0) CHG

　LEFTJOIN dbo.testTable DATA

　ON DATA.id = CHG.id; 

Step4.3: 不通过事务，连续更新Int类型列值2次

UPDATE dbo.testTable SET col1= 33

WHERE id = 3;

UPDATE dbo.testTable SET col1 = 333

WHERE id = 3;

  观察[查询结果]:

　SELECT  CHANGE_TRACKING_CURRENT_VERSION()as CHANGETRACKING_CURRENTVERSION ,

   　　CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.testTable'))as CHANGETRACKING_MINVALIDVERSION, *

　FROM

　　CHANGETABLE(CHANGES dbo.testTable, 0) CHG

　LEFTJOIN dbo.testTable DATA

　ON DATA.id = CHG.id; 

备注：　　当更新值col1为 33时， SYS_CHANGE_VERSION = 5; 当更新值col1为 333时， SYS_CHANGE_VERSION = 6;
 　　　　  和事务下更新2次不一样。
Step4.4: 更新XML 数据

UPDATE dbo.testTable SET
    col5.modify('replace value of /a[1]/text()[1] with "replace"')
WHERE id = 4;
 
UPDATE dbo.testTable SET
    col5.modify('insert <a>1</a> as last into /')
WHERE id = 5;

  观察[查询结果]:

　SELECT  CHANGE_TRACKING_CURRENT_VERSION()as CHANGETRACKING_CURRENTVERSION ,

   　　CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.testTable'))as CHANGETRACKING_MINVALIDVERSION, *

　FROM

　　CHANGETABLE(CHANGES dbo.testTable, 0) CHG

　LEFTJOIN dbo.testTable DATA

　ON DATA.id = CHG.id; 

 Step4.5: 更新Varbinary（Max）数据

UPDATE dbo.testTable 
SET col4= col4 + 0x12345

WHERE

id = 6;

  观察[查询结果]:

　SELECT  CHANGE_TRACKING_CURRENT_VERSION()as CHANGETRACKING_CURRENTVERSION ,

 　　CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.testTable'))as CHANGETRACKING_MINVALIDVERSION, *

　FROM

　　CHANGETABLE(CHANGES dbo.testTable, 0) CHG

　LEFTJOIN dbo.testTable DATA

　ON DATA.id = CHG.id; 

Step5：更新主键之测试

UPDATE dbo.testTable SET id = 11

WHERE id = 1;


INSERT

 dbo.testTable(id,col1, col2, col3, col4, col5)

VALUES

(1, 1,'AA','AAA', 0x1,'<a>aa</a>')

  观察[查询结果]:

　SELECT  CHANGE_TRACKING_CURRENT_VERSION()as CHANGETRACKING_CURRENTVERSION ,

 　　CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.testTable'))as CHANGETRACKING_MINVALIDVERSION, *

　FROM

　　CHANGETABLE(CHANGES dbo.testTable, 0) CHG

　LEFTJOIN dbo.testTable DATA

　ON DATA.id = CHG.id;