SqlServer 更改跟踪(Chang Tracking)
来源:互联网 发布:windows下重启nginx 编辑:程序博客网 时间:2024/05/19 05:06
对于跟踪数据库表的 DML 操作,SQLserver 2008 及以上版本提供了 变更数据捕获和更改跟踪。
变更数据库捕获 与 跟踪更改 的区别:
变更数据捕获与更改跟踪都是记录表的DML操作
变更数据捕获可把操作数据的历史值保存下来;更改跟踪捕获更改了表行这一事实,但不会捕获更改的数据。
变更数据捕获使用异步进程捕获,该进程扫描事务日志;更改跟踪同步跟踪DML操作
变更数据捕获存储在当前数据库system表中,更改表可指定存储位置;更改跟踪表存储在系统架构sys中,不可查看结构定义
【接下来测试】
创建测试表:
USE [MyDatabase]GOCREATE TABLE [dbo].[TestTab]([id] [int] NOT NULL,[name] [varchar](50) NOT NULL,[insertDate] [datetime] NOT NULL,[value] [numeric](14, 4) NULL,[info] [varchar](20) NULL,CONSTRAINT [PK_TestTab] PRIMARY KEY CLUSTERED ([id] ASC)ON [PRIMARY]) ON [PRIMARY]GO
启用数据库更改跟踪:(参考:ALTER DATABASE SET 选项 (Transact-SQL))
USE [master]GOALTER DATABASE [MyDatabase] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON)GO
启用表的更改跟踪:(参考:ALTER TABLE (Transact-SQL))
USE [MyDatabase]GOALTER TABLE [dbo].[TestTab] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)GO
查看数据库或表启用情况:(参考:sys.change_tracking_databases,sys.change_tracking_tables)
SELECT DB_NAME(database_id) DBName,retention_period,retention_period_units_desc,is_auto_cleanup_onFROM sys.change_tracking_databases SELECT OBJECT_NAME(object_id) TableName,is_track_columns_updated_onFROM sys.change_tracking_tables
那跟踪表的数据存储在哪呢?使用内部表可查看
-- 每个启用跟踪的表保存一行SELECT * FROM sys.internal_tables WHERE internal_type_desc='CHANGE_TRACKING'SELECT * FROM sys.sysobjects WHERE id = (SELECT object_id FROM sys.internal_tables WHERE internal_type_desc='CHANGE_TRACKING'AND parent_object_id=object_id('TestTab'))--查看表大小exec sp_spaceused 'sys.change_tracking_37575172'exec sp_spaceused 'sys.syscommittab'
插入测试数据:
--插入测试数据insert into [dbo].[TestTab](id,name,insertDate,value,info)select 1,'kk',GETDATE(),10,'info'union allselect 2,'GG',GETDATE(),50,''union allselect 3,'MM',GETDATE(),0,null
怎么查看跟踪信息,系统提供了一个表值函数 CHANGETABLE :
--返回<版本号>之后的所有行更改信息(如:查看版本号为0及之后的所有更改信息)--SELECT * FROM CHANGETABLE(CHANGES <表名>,<版本号>) as TSELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,0) as T--返回指定行的最新更改跟踪信息(如: id=3 最新更改情况)--SELECT * FROM CHANGETABLE(VERSION <表名>,(<主键列>),(<主键值>)) as TSELECT * FROM CHANGETABLE(VERSION dbo.TestTab,(id),(3)) as T
CHANGETABLE CHANGES:
列名
数据类型
说明
SYS_CHANGE_VERSION
bigint
与上次对行的更改关联的版本值。(同一批次操作,版本号相同)
SYS_CHANGE_CREATION_VERSION
bigint
与上次插入操作关联的版本值。(同一批次操作,版本号相同)
SYS_CHANGE_OPERATION
nchar(1)
指定更改的类型:
U = 更新
I = 插入
D = 删除
SYS_CHANGE_COLUMNS
varbinary(4100)
列出自基准版本以后发生了更改的列。
[注意]
计算列永远不会作为更改的列列出。
以下任何一个条件为真时,值为 NULL:
*未启用列更改跟踪。
*操作是插入操作或删除操作。
*在一个操作中更新了所有非主键列。不应直接解释此二进制值。
SYS_CHANGE_CONTEXT
varbinary(128)
更改可以在 INSERT、UPDATE 或 DELETE 语句中使用 WITH 子句选择指定的上下文信息。
<<主键列值>>
与用户表列相同
被跟踪表的主键值。这些值在用户表中唯一标识各行。(与当前表连接反应最新数据)
CHANGETABLE VERSION :
列名
数据类型
说明
SYS_CHANGE_VERSION
bigint
与行关联的当前更改版本值。
如果在超过更改跟踪保留期的时段内没有进行更改,或者在启用更改跟踪之后未更改行,则值为 NULL。
SYS_CHANGE_CONTEXT
varbinary(128)
更改可以在 INSERT、UPDATE 或 DELETE 语句中使用 WITH 子句选择指定的上下文信息。
<<主键列值>>
与用户表列相同
被跟踪表的主键值。这些值在用户表中唯一标识各行。(与当前表连接反应最新数据)
更改数据:
update [dbo].[TestTab] set insertDate = GETDATE(),info = 'update' where id=3update [dbo].[TestTab] set insertDate = GETDATE(),value = 100 where id=3 --执行了2次
检查版本号信息:(参考:CHANGE_TRACKING_MIN_VALID_VERSION,CHANGE_TRACKING_CURRENT_VERSION)
--获取一个表的最低有效版本号SELECT CHANGE_TRACKING_MIN_VALID_VERSION( OBJECT_ID('dbo.TestTab'))--返回与上次提交的事务相关联的版本,以确定下次需要更改时将使用的版本SELECT CHANGE_TRACKING_CURRENT_VERSION()
--------------------0(1 行受影响)--------------------4(1 行受影响)
--或者SELECT min_valid_version,begin_versionFROM sys.change_tracking_tables WHERE OBJECT_NAME(object_id) = 'TestTab'select MAX(commit_ts) from sys.dm_tran_commit_table
再次用CHANGETABLE 查看:
SELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,0) as T
为什么不是更新(SYS_CHANGE_OPERATION = 'U')??
last_sync_version = 0 为当时的跟踪情况,现在 SYS_CHANGE_VERSION 已经大于1 了,要查询各状态当时更改情况,如:
--返回最后一次版本号之后的更改(需指定 last_sync_version )--该值 last_sync_version 必须保持记住,下次可以现在查到的@last_sync_version = CHANGE_TRACKING_CURRENT_VERSION()SELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,0) as TSELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,1) as TSELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,2) as TSELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,3) as T
注:最后两次更改是相同的的,所以 SYS_CHANGE_COLUMNS 一样。
还可以使用 CHANGE_TRACKING_IS_COLUMN_IN_MASK查看更改列情况 (需启用 TRACK_COLUMNS_UPDATED = ON )
--检查更改跟踪(查看哪些列曾更改)SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK (<column_id>,<change_columns>)--column_id : 是正在被检查的列的 ID。 可以使用 COLUMNPROPERTY 函数获取此列 ID。--change_columns : 是 CHANGETABLE 数据的 SYS_CHANGE_COLUMNS 列中的二进制数据。--(如主键列更改,视为删除旧值,插入新值)SELECT id,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'id','COLUMNID'),SYS_CHANGE_COLUMNS) id_is_changed,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'name','COLUMNID'),SYS_CHANGE_COLUMNS) name_is_changed,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'insertDate','COLUMNID'),SYS_CHANGE_COLUMNS) insertdate_is_changed,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'value','COLUMNID'),SYS_CHANGE_COLUMNS) value_is_changed,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'info','COLUMNID'),SYS_CHANGE_COLUMNS) info_is_changedFROM CHANGETABLE(CHANGES dbo.TestTab,1) AS TWHERE SYS_CHANGE_OPERATION='U'
在进行删除和插入操作:
delete from [dbo].[TestTab] where id=1insert into [dbo].[TestTab](id,name,insertDate,value,info)select 4,'kk',GETDATE(),10,'info'
--查看版本号为3之后都操作了什么?SELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,3) as T
对于当前表的记录是:
SELECT T.id,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,D.*FROM CHANGETABLE(CHANGES dbo.TestTab,3) AS TLEFT JOIN [dbo].[TestTab] AS D ON T.id = D.id
对于数据定时转移的情况:源表中数据进行了 insert,update,delete 操作目标表操作如下:insert : 跟踪表关联源表,查询到的数据插入到目标表中.(即使插入后的数据发送更改,在改版本号之显示还是操作插入"I")update : 跟踪表关联源表,查询到的数据更新到目标表中.也可使用函数 CHANGE_TRACKING_IS_COLUMN_IN_MASK 更新了哪些列delete : 对于删除 (SYS_CHANGE_OPERATION = 'D'),跟踪表直接对目标表进行删除(注意主键不可相同!否则可能删错)
--数据转移情况,根据版本号更改,所以版本号要记住!源表: [TestTab]目标表: [TargetTestTab]--insert : INSERT INTO [TargetTestTab](id,name,insertDate,value,info)SELECT D.id,name,insertDate,value,infoFROM CHANGETABLE(CHANGES dbo.TestTab,3) AS TINNER JOIN [dbo].[TestTab] AS D ON T.id = D.idWHERE SYS_CHANGE_OPERATION = 'I'--update : UPDATE K SET K.id=D.id,K.name=D.name,K.insertDate=D.insertDate,K.value=D.value,K.info=D.infoFROM CHANGETABLE(CHANGES dbo.TestTab,3) AS TINNER JOIN [dbo].[TestTab] AS D ON T.id = D.idINNER JOIN [dbo].[TargetTestTab] K ON D.id=K.idWHERE SYS_CHANGE_OPERATION = 'U'--delete : DELETE FROM [dbo].[TargetTestTab] WHERE id IN(SELECT id FROM CHANGETABLE(CHANGES dbo.TestTab,3) AS TWHERE SYS_CHANGE_OPERATION = 'D')
转移数据前,先查看当前的版本号 CHANGE_TRACKING_CURRENT_VERSION() ,例如500 。而上面的操作中,版本号为3,这批数据相应更改完成后,下次从版本号500开始。当然可能有误差,因为在操作这批数据时,当前可能有其他操作,版本号也在累计。
建议使用快照隔离级别,保证事务的一致性:
ALTER DATABASE [MyDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON;
最后禁用更改跟踪:
--对表禁用ALTER TABLE [dbo].[TestTab] DISABLE CHANGE_TRACKING GO--对数据库禁用ALTER DATABASE [MyDatabase] SET CHANGE_TRACKING = OFF GO
附件:
--查看跟踪表大小 select sct1.name as CT_schema, sot1.name as CT_table, ps1.row_count as CT_rows, ps1.reserved_page_count*8./1024. as CT_reserved_MB, sct2.name as tracked_schema, sot2.name as tracked_name, ps2.row_count as tracked_rows, ps2.reserved_page_count*8./1024. as tracked_base_table_MB, change_tracking_min_valid_version(sot2.object_id) as min_valid_versionFROM sys.internal_tables itJOIN sys.objects sot1 on it.object_id=sot1.object_idJOIN sys.schemas AS sct1 on sot1.schema_id=sct1.schema_idJOIN sys.dm_db_partition_stats ps1 on it.object_id = ps1. object_id and ps1.index_id in (0,1)LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_idLEFT JOIN sys.schemas AS sct2 on sot2.schema_id=sct2.schema_idLEFT JOIN sys.dm_db_partition_stats ps2 on sot2.object_id = ps2. object_id and ps2.index_id in (0,1)WHERE it.internal_type IN (209, 210);GO
参考:
更改跟踪概述
更改跟踪
使用更改跟踪函数获取更改
- SqlServer 更改跟踪(Chang Tracking)
- 【SQL Server学习笔记】更改跟踪(Chang Tracking)
- sqlserver 更改跟踪案例
- SQL Server 的更改跟踪 (Change Tracking)
- Session Tracking(会话跟踪)
- 跟踪(Tracking)资料汇总
- 块修改跟踪 (Block Change Tracking)
- template tracking(基于模板的跟踪)
- 块变更跟踪(Block change tracking)
- 网站跟踪(web tracking)的原理
- Block Change Tracking(块跟踪)详解
- Fast Compressive Tracking (高速压缩跟踪)
- Tracking-Learning-Detection (TLD跟踪算法)
- 启用或禁用更改块跟踪 (Changed Block Tracking, CBT)的两种方式
- 启用或禁用更改块跟踪 (Changed Block Tracking, CBT)的两种方式
- 压缩跟踪Compressive Tracking
- 压缩跟踪Compressive Tracking
- 压缩跟踪Compressive Tracking
- Android开发之InstanceState详解
- 设置PL/SQL编码
- wait和notify实例
- 彩色图像灰度化
- Python:使用ctypes库调用外部DLL
- SqlServer 更改跟踪(Chang Tracking)
- 手动控制ScrollView滚动,ScrollView添加子布局,自动向上滚动
- 配置数据库连接
- ARM基础学习收集!!!!!------阿冬专栏
- ubuntu屏幕亮度无法调节的问题
- Java游戏之碰撞检测
- 大话设计模式C++版——原则和引言
- Mac中Finder窗口显示绝对路径
- 这是我的第一篇博客文章