【转载，备忘】SQL Server 更改跟踪（Chang Tracking）监控表数据

2023-08-22 21:51:28

一.本文所涉及的内容（Contents）

二.背景（Contexts）

　　在SQL Server 2008以上版本中，对数据库中的用户表所做的 DML 更改（插入、更新和删除操作）除了：SQL Server 变更数据捕获（CDC）监控表数据之外，还有一个新增功能，那就是：更改跟踪（Chang Tracking），它跟CDC有什么不同呢？使用场景有什么区别呢？

三.主要区别与对比（Compare）

1. SQL Server 2008 引入了两项跟踪功能：变更数据捕获和更改跟踪，以使应用程序能够确定对数据库中的用户表所做的 DML 更改（插入、更新和删除操作）；

2. 如果应用程序需要有关所有所做更改的信息以及所更改数据的中间值，则可能适合使用变更数据捕获，而不适合使用更改跟踪。有关详细信息，请参阅比较变更数据捕获和更改跟踪和变更数据捕获。

3. 变更数据捕获使用异步进程捕获，此进程读取事务日志；更改跟踪是与DML操作同步的，不需要读取事务日志；

4. 变更数据捕获包含了变更的历史记录，更改跟踪只保存行，但不会捕获更改的数据；

更改跟踪可以根据外部传入的应用程序上下文，来完成更细颗粒度的更改处理，参考：WITH CHANGE_TRACKING_CONTEXT

四.实现监控表数据步骤（Process）

/******* Step1:创建示例数据库*******/

USE MASTER

GO

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'CT_DB')

DROP DATABASE CT_DB

GO

CREATE DATABASE CT_DB

GO

(二) 开启数据库更改跟踪Chang Tracking，通过下面的SQL脚本可以查询开启了更改跟踪的数据库列表；

/******* Step2:开启数据库更改跟踪Chang Tracking *******/

--启用更改跟踪（Chang Tracking），天清理一次（HOURS）

ALTER DATABASE CT_DB

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS,

AUTO_CLEANUP = ON)

--查看数据库是否启用更改跟踪

SELECT DB_NAME(database_id) DataBaseName,is_auto_cleanup_on,

retention_period,retention_period_units_desc

FROM sys.change_tracking_databases

（Figure1：查看数据库是否启用更改跟踪）

(三) 开启数据库更改跟踪Chang Tracking，通过下面的SQL脚本可以查询开启了更改跟踪的数据库列表；

/******* Step3:对表启用更改跟踪*******/

--创建测试表

USE CT_DB

GO

CREATE TABLE [dbo].[Department](

    [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,

    [Name] [nvarchar](200) NULL,

    [GroupName] [nvarchar](50) NOT NULL,

    [ModifiedDate] [datetime] NOT NULL,

    [AddName] [nvarchar](120) NULL,

 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

(

    [DepartmentID] ASC

) ON [PRIMARY]

) ON [PRIMARY]

GO

--对表启用更改跟踪

ALTER TABLE  [dbo].[Department]

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON)

--查看表是否启用更改跟踪

SELECT OBJECT_NAME(object_id) TableName,is_track_columns_updated_on

FROM sys.change_tracking_tables

（Figure2：查看表是否启用更改跟踪）

(四) 测试对Department表进行DML操作：分两次把数据插入到表，并使用下面的SQL脚本查看变更跟踪信息，从中可以发现，两次不同的插入会生成两个版本，这可以证明变更跟踪是跟DML操作是同步的；

/******* Step4:测试DML变更跟踪*******/

--测试插入数据（版本将变成1）

INSERT  INTO dbo.Department(

    Name ,

    GroupName ,

    ModifiedDate

)VALUES('Marketing','Sales and Marketing',GETDATE())

--再次测试插入数据（版本将变成2）

INSERT  INTO dbo.Department(

    Name ,

    GroupName ,

    ModifiedDate

)VALUES

('Production','Manufacturing',GETDATE()),

('Purchasing','Inventory Management',GETDATE())

--表记录

SELECT * FROM dbo.Department

--当前版本

SELECT CHANGE_TRACKING_CURRENT_VERSION ()

AS CURRENT_VERSION

--最小版本

SELECT CHANGE_TRACKING_MIN_VALID_VERSION

(OBJECT_ID('dbo.Department')) AS MIN_VERSION

--使用Changes关键字查看更改信息

SELECT DepartmentID,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION

FROM CHANGETABLE(CHANGES dbo.Department, 0) AS CT

（Figure3：更改跟踪信息）

(五) 继续测试对Department表进行DML操作：做一次Update操作、做一次Delete操作；

--测试更新数据（版本将变成3）

UPDATE dbo.Department SET Name = 'Marketing Group',ModifiedDate = GETDATE()

WHERE Name = 'Marketing'

--测试删除数据（版本将变成4）

DELETE FROM dbo.Department WHERE Name='Production'

--表记录

SELECT * FROM dbo.Department

--当前版本

SELECT CHANGE_TRACKING_CURRENT_VERSION ()

AS CURRENT_VERSION

--最小版本

SELECT CHANGE_TRACKING_MIN_VALID_VERSION

(OBJECT_ID('dbo.Department')) AS MIN_VERSION

--查看版本2之后的更改

SELECT DepartmentID,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,SYS_CHANGE_COLUMNS

FROM CHANGETABLE(CHANGES dbo.Department, 2) AS CT

（Figure4：更改跟踪信息）

(六) 查看列变更说明；

--返回哪些列被修改，1为真,0为假

SELECT DepartmentID,

CHANGE_TRACKING_IS_COLUMN_IN_MASK(

COLUMNPROPERTY(

OBJECT_ID('dbo.Department'),'Name', 'ColumnId') ,

SYS_CHANGE_COLUMNS) '是否改变Name',

CHANGE_TRACKING_IS_COLUMN_IN_MASK(

COLUMNPROPERTY(

OBJECT_ID('dbo.Department'),'GroupName', 'ColumnId') ,

SYS_CHANGE_COLUMNS) '是否改变GroupName',

CHANGE_TRACKING_IS_COLUMN_IN_MASK(

COLUMNPROPERTY(

OBJECT_ID('dbo.Department'), 'ModifiedDate', 'ColumnId') ,

SYS_CHANGE_COLUMNS) '是否改变ModifiedDate'

FROM CHANGETABLE(CHANGES dbo.Department, 2) AS CT

WHERE SYS_CHANGE_OPERATION = 'U'

（Figure5：列变更说明）

(七) 使用Version关键字查看更改信息；

--使用Version关键字查看更改信息

SELECT *

FROM dbo.Department d

CROSS APPLY CHANGETABLE(VERSION dbo.Department, (DepartmentID), (d.DepartmentID)) AS ct

（Figure6：Version关键字查看更改信息）

(八) 通过在外部应用程序中的上下文信息判断这个DML是由哪个应用产生的；

--设置跟踪外部程序上下文信息

DECLARE @context VARBINARY(128) = CAST('我要插入记录' AS VARBINARY(128));

WITH CHANGE_TRACKING_CONTEXT (@context)

--测试插入数据（版本将变成）

INSERT  INTO dbo.Department(

    Name ,

    GroupName ,

    ModifiedDate

)VALUES('Document Control','Quality Assurance',GETDATE())

--查询Context更改

SELECT DepartmentID,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,

CAST(SYS_CHANGE_CONTEXT AS VARCHAR) ApplicationContext

FROM CHANGETABLE(CHANGES dbo.Department, 4) AS CT

（Figure7：上下文信息）

(九) 获取更改跟踪版本2之后的表数据；

--获取更改跟踪版本2之后的表数据

SELECT SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,SYS_CHANGE_COLUMNS,D.*

FROM CHANGETABLE(CHANGES dbo.Department, 2) AS CT

LEFT JOIN dbo.Department AS D

ON CT.DepartmentID = D.DepartmentID

（Figure8：更改表记录）

(十) 总结：在更改跟踪的记录中包括了表Department 的唯一编号，还有DML的操作符字段SYS_CHANGE_OPERATION，枚举这些值（I=Insert、U=Update、D=Delete），还有DML操作的版本号：SYS_CHANGE_VERSION，它是每进行一次DML，都会递增一个版本号，所以你可以针对I=Insert、U=Update、D=Delete不同的类型加上版本号过滤，就可以找到那些数据进行了更新；

五.参考文献（References）

SQL Server 2008中新增的变更数据捕获（CDC）和更改跟踪

跟踪数据更改

码农公寓

一.本文所涉及的内容（Contents）

二.背景（Contexts）

三.主要区别与对比（Compare）

四.实现监控表数据步骤（Process）

五.参考文献（References）

相关文章