更改跟踪(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
转载于:https://blog.51cto.com/yupeigu/1368113