Sql Server之更改跟踪功能

1.更改跟踪（Change Tracking）介绍

更改跟踪是一种轻量型解决方案，它为应用程序提供了一种有效的更改跟踪机制。更改跟踪捕获更改了表行这一事实，但不会捕获更改的数据。 这样，应用程序就可以确定使用从用户表中直接获取的最新行数据更改的行。

2.启用更改跟踪（Change Tracking）

（1）启用更改跟踪限制
1）先启用数据库的更改跟踪，再启用各个表的更改跟踪
2）若要使用更改跟踪，必须将数据库兼容级别设为 90 或更高。 如果数据库的兼容级别低于 90，则可以配置更改跟踪。 但是，用于获取更改跟踪信息的 CHANGETABLE 函数将返回错误。
3）使用快照隔离是帮助确保所有更改跟踪信息保持一致的最简单方式。 因此，强烈建议将数据库的快照隔离设为 ON

（2）启用数据库更改跟踪（Change Tracking）
查询某个数据库是否开启更改跟踪（Change Tracking）功能

SELECT
DB_NAME(database_id) DataBaseName,is_auto_cleanup_on,retention_period,retention_period_units_desc
FROM sys.change_tracking_databases
WHERE DB_NAME(database_id)='数据库名称'

开启某个数据库的更改跟踪（Change Tracking）功能

ALTER DATABASE 数据库名称
SET CHANGE_TRACKING = ON --开启更改跟踪功能
(CHANGE_RETENTION = 30 DAYS, AUTO_CLEANUP = ON) --设置保持期与是否自动清除

（3）启用表更改跟踪（Change Tracking）
查询某个表是否开启更改跟踪（Change Tracking）功能

SELECT
OBJECT_NAME(object_id) TableName,is_track_columns_updated_on
FROM sys.change_tracking_tables
WHERE OBJECT_NAME(object_id)='表名'

开启某个表的更改跟踪（Change Tracking）功能

ALTER TABLE [schema].[表名]
ENABLE CHANGE_TRACKING --开启更改跟踪服务
WITH (TRACK_COLUMNS_UPDATED = ON) --开启跟踪已更新的列

3.禁用更改跟踪（Change Tracking）

注：必须首先为所有启用了更改跟踪的表禁用更改跟踪，然后才能禁用数据库的更改跟踪功能

（1）禁用表更改跟踪（Change Tracking）

ALTER TABLE [schema].[表名]  
DISABLE CHANGE_TRACKING;

（2）禁用数据库更改跟踪（Change Tracking）

ALTER DATABASE 数据库名称 
SET CHANGE_TRACKING = OFF

4.更改跟踪（Change Tracking）常用SQL语句

（1）查询当前更改跟踪版本号

SELECT CHANGE_TRACKING_CURRENT_VERSION ()
AS CURRENT_VERSION

（2）查询在数据库更改跟踪功能保持期内的最小更改跟踪版本号

SELECT CHANGE_TRACKING_MIN_VALID_VERSION
(OBJECT_ID('schema.表名')) AS MIN_VERSION

（3）使用Changes关键字查看更改信息

SELECT * -- SYS_CHANGE_VERSION,SYS_CHANGE_CREATION_VERSION,SYS_CHANGE_OPERATION,SYS_CHANGE_COLUMNS,SYS_CHANGE_CONTEXT,表主键字段名称
FROM CHANGETABLE(CHANGES schema.表名, 更改跟踪版本号) AS CT --只能查询大于更改跟踪版本号的数据变化信息

（4）返回哪些列被修改，1为真,0为假

SELECT 表主键名称,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('schema.表名'),'字段名1', 'ColumnId') ,
SYS_CHANGE_COLUMNS) '是否改变字段名1',
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('schema.表名'),'字段名2', 'ColumnId') ,
SYS_CHANGE_COLUMNS) '字段名2',
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('schema.表名'), '字段名3', 'ColumnId') ,
SYS_CHANGE_COLUMNS) '字段名3'
-- 可增加要查看的列
FROM CHANGETABLE(CHANGES 'schema.表名', 更改跟踪版本号) AS CT
WHERE SYS_CHANGE_OPERATION = 'U' -- U代表更新、I代表插入、D代表删除