【SQL Server学习笔记】更改跟踪(Chang Tracking)

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

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

--1.建库
if DB_ID('ck') is not null
	drop database ck
go

create database ck
go


--2.1启动数据库的更改跟踪
alter database ck
set change_tracking = on
(
	change_retention = 36 hours,  --保留期限影响数据库维护多少更改跟踪
	auto_cleanup = on             --是否启用自动清除
)
go


--2.2使用更改跟踪的最佳实践是为数据库启用快照隔离，
--对于有DML操作的数据库、表，可以一致的方式捕获更改跟踪的信息，
--抓取最新版本并使用该版本号来获取适当的数据
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 = 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('McGill',1),
      ('Smith',0),
      ('University',1)  --一次添加了3条数据，共享一个版本号


--4.2返回表中最后提交的事务中的版本号,在更改跟踪表上的DML操作使版本号变大      
select CHANGE_TRACKING_CURRENT_VERSION()     --显示1

--下面另个更新操作会使版本号增加2====================================
update dbo.tt
set v = 'wc11'
where vid = 1

update dbo.tt
set v = 'wc22'
where vid = 2

select CHANGE_TRACKING_CURRENT_VERSION()   --显示3

--在beging tran事务中的多个操作会共享一个版本号	
begin tran
	update dbo.tt
	set v = 'wc111'
	where vid = 1

	update dbo.tt
	set v = 'wc22'
	where vid = 2
commit tran

select CHANGE_TRACKING_CURRENT_VERSION()  --显示4
--======================================================================

--4.3返回更改跟踪表的最小可用版本
select CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.tt'))  --显示0


/*============================================================================
4.4changetable函数中的changs关键字查看从指定的版本号以来的更改，
   不包含指定版本号本身的变化

注意：第二参数是版本号，与变更捕获中的LSN作用是类似的，指定不同的版本号，
      会返回不同的记录
      
比如添加一条记录，此时版本号从0变为1，再更新刚才添加的记录，版本号由1变为2：

1.如果指定版本号为0，那么不会返回版本号从0到1的变更(一条insert操作)以及
  版本号从1到2的变更(一条update操作)，而只返回一条insert操作，
  因为返回的其实是从指定版本号开始的所有净更改

2.如果指定的版本号是1,那么会返回从1到2之间的变化，也就是那个更新操作，
  同时sys_change_columns会显示哪一列被更新了
==============================================================================*/
select vid,
	   sys_change_operation,   --I是Insert,U是Update,D是Delete
	   sys_change_version,     --版本号
	   sys_change_columns      --哪一列被更改了
from changetable(changes dbo.tt,    --表名
                         2) as x    --版本号


--4.5列是否被修改过
select vid,
	   CHANGE_TRACKING_IS_COLUMN_IN_MASK(                --列的id
							columnproperty(object_id('dbo.tt'),  --表名
										   'v',                  --列名
										   'ColumnId'),        --返回列id
								sys_change_columns),     --改变掩码
								
       CHANGE_TRACKING_IS_COLUMN_IN_MASK(
							columnproperty(object_id('dbo.tt'),
								           'vv',
								           'ColumnId'),
								sys_change_columns)
from changetable(changes dbo.tt,8) as x


--4.6changetable函数中的version关键字查看：每行的最新更改跟踪版本
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查看更改跟踪应用程序上下文的信息
--可以跟踪哪一个应用程序对哪些行进行了修改，可用于对多个数据源中的数据进行同步
declare @app_context varbinary(128) = cast('wc1_data_source' as varbinary(128));

;with change_tracking_context(@app_context)  --表示调用的应用程序
insert into dbo.tt(v,vv)
values('wc1_data_source',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