【SQLServer学习笔记】更改跟踪(ChangTracking)

更改跟踪(Chang Tracking)是一个同步过程&＃xff0c;是DML操作本身(insert、update、delete)事务的一部分&＃xff0c;使用它可以以最小的磁盘存储开销来侦测净行变化。

更改跟踪的同步行为允许修改的数据以事务一致性的形式表现&＃xff0c;也具有监测数据冲突的能力。应用程序可以最小的性能开销使用此功能&＃xff0c;不需要自定义的检测更改的触发器或表的时间戳。

--1.建库
if DB_ID(&＃39;ck&＃39;) is not nulldrop database ck
gocreate database ck
go--2.1启动数据库的更改跟踪
alter database ck
set change_tracking &＃61; on
(change_retention &＃61; 36 hours, --保留期限影响数据库维护多少更改跟踪auto_cleanup &＃61; on --是否启用自动清除
)
go--2.2使用更改跟踪的最佳实践是为数据库启用快照隔离&＃xff0c;
--对于有DML操作的数据库、表&＃xff0c;可以一致的方式捕获更改跟踪的信息&＃xff0c;
--抓取最新版本并使用该版本号来获取适当的数据
alter database ck
set allow_snapshot_isolation on
go--2.3查看数据库是否启用更改跟踪
select DB_NAME(database_id),is_auto_cleanup_on,retention_period,retention_period_units,retention_period_units_desc
from sys.change_tracking_databases--3.1建表
create table dbo.tt(vid int not null identity(1,1) primary key,v varchar(100) not null,vv bit not null)
go--3.2启用表的更改跟踪
alter table dbo.tt
enable change_tracking
with (track_columns_updated &＃61; on ) --跟踪哪些列被更改过 --3.3查看表是否启用了更改跟踪
select OBJECT_NAME(object_id),is_track_columns_updated_on
from sys.change_tracking_tables--4.1添加数据
insert into dbo.tt(v,vv)
values(&＃39;McGill&＃39;,1),(&＃39;Smith&＃39;,0),(&＃39;University&＃39;,1) --一次添加了3条数据&＃xff0c;共享一个版本号--4.2返回表中最后提交的事务中的版本号,在更改跟踪表上的DML操作使版本号变大
select CHANGE_TRACKING_CURRENT_VERSION() --显示1--下面另个更新操作会使版本号增加2&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
update dbo.tt
set v &＃61; &＃39;wc11&＃39;
where vid &＃61; 1update dbo.tt
set v &＃61; &＃39;wc22&＃39;
where vid &＃61; 2select CHANGE_TRACKING_CURRENT_VERSION() --显示3--在beging tran事务中的多个操作会共享一个版本号
begin tranupdate dbo.ttset v &＃61; &＃39;wc111&＃39;where vid &＃61; 1update dbo.ttset v &＃61; &＃39;wc22&＃39;where vid &＃61; 2
commit transelect CHANGE_TRACKING_CURRENT_VERSION() --显示4
--&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;--4.3返回更改跟踪表的最小可用版本
select CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(&＃39;dbo.tt&＃39;)) --显示0/*&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
4.4changetable函数中的changs关键字查看从指定的版本号以来的更改&＃xff0c;不包含指定版本号本身的变化注意&＃xff1a;第二参数是版本号&＃xff0c;与变更捕获中的LSN作用是类似的&＃xff0c;指定不同的版本号&＃xff0c;会返回不同的记录比如添加一条记录&＃xff0c;此时版本号从0变为1&＃xff0c;再更新刚才添加的记录&＃xff0c;版本号由1变为2&＃xff1a;1.如果指定版本号为0&＃xff0c;那么不会返回版本号从0到1的变更(一条insert操作)以及版本号从1到2的变更(一条update操作)&＃xff0c;而只返回一条insert操作&＃xff0c;因为返回的其实是从指定版本号开始的所有净更改2.如果指定的版本号是1,那么会返回从1到2之间的变化&＃xff0c;也就是那个更新操作&＃xff0c;同时sys_change_columns会显示哪一列被更新了
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;*/
select vid,sys_change_operation, --I是Insert,U是Update,D是Deletesys_change_version, --版本号sys_change_columns --哪一列被更改了
from changetable(changes dbo.tt, --表名2) as x --版本号--4.5列是否被修改过
select vid,CHANGE_TRACKING_IS_COLUMN_IN_MASK( --列的idcolumnproperty(object_id(&＃39;dbo.tt&＃39;), --表名&＃39;v&＃39;, --列名&＃39;ColumnId&＃39;), --返回列idsys_change_columns), --改变掩码CHANGE_TRACKING_IS_COLUMN_IN_MASK(columnproperty(object_id(&＃39;dbo.tt&＃39;),&＃39;vv&＃39;,&＃39;ColumnId&＃39;),sys_change_columns)
from changetable(changes dbo.tt,8) as x--4.6changetable函数中的version关键字查看&＃xff1a;每行的最新更改跟踪版本
select tt.vid,tt.v,tt.vv,x.sys_change_version
from dbo.tt
cross apply changetable(version dbo.tt, --表名(vid), --按任意顺序指定一个或多个主键列名(tt.vid) --按主键列名的顺序指定主键对应的值) as x --必须指定别名--5查看更改跟踪应用程序上下文的信息
--可以跟踪哪一个应用程序对哪些行进行了修改&＃xff0c;可用于对多个数据源中的数据进行同步
declare &＃64;app_context varbinary(128) &＃61; cast(&＃39;wc1_data_source&＃39; as varbinary(128));;with change_tracking_context(&＃64;app_context) --表示调用的应用程序
insert into dbo.tt(v,vv)
values(&＃39;wc1_data_source&＃39;,1)select Vid,sys_change_creation_version,sys_change_operation,sys_change_version,sys_change_context, --应用程序上下文信息CAST(sys_change_context as varchar) --显示修改这条记录的应用程序
from changetable(changes dbo.tt,9) as x