SQL Server 更改跟踪（Chang Tracking）监控表数据

2023-10-09 10:30
文章标签 sql 数据 监控 server 更改 跟踪 database tracking chang

本文主要是介绍SQL Server 更改跟踪（Chang Tracking）监控表数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

原文: SQL Server 更改跟踪（Chang Tracking）监控表数据

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

  1. 本文所涉及的内容（Contents）
  2. 背景（Contexts）
  3. 主要区别与对比（Compare）
  4. 实现监控表数据步骤（Process）
  5. 参考文献（References）

二.背景（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

wps_clip_image-12450

（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

wps_clip_image-20661

（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

wps_clip_image-13828

（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

wps_clip_image-7154

（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'

wps_clip_image-13637

（Figure5：列变更说明）

 

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

--使用Version关键字查看更改信息
SELECT *
FROM dbo.Department d
CROSS APPLY CHANGETABLE(VERSION dbo.Department, (DepartmentID), (d.DepartmentID)) AS ct

wps_clip_image-8676

（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

wps_clip_image-9332

（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

wps_clip_image-11505

（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）和更改跟踪

跟踪数据更改

Oracle 一样能够Flashback

这篇关于SQL Server 更改跟踪（Chang Tracking）监控表数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/172219。

相关文章

一文教你Python如何快速精准抓取网页数据

一文教你Python如何快速精准抓取网页数据

《一文教你Python如何快速精准抓取网页数据》这篇文章主要为大家详细介绍了如何利用Python实现快速精准抓取网页数据,文中的示例代码简洁易懂,具有一定的借鉴价值,有需要的小伙伴可以了解下... 目录1. 准备工作2. 基础爬虫实现3. 高级功能扩展3.1 抓取文章详情3.2 保存数据到文件4. 完整示例
阅读更多...
MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

《MySQL多表连接操作方法(INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN)》多表连接是一种将两个或多个表中的数据组合在一起的SQL操作,通过连接,... 目录一、 什么是多表连接？二、 mysql 支持的连接类型三、 多表连接的语法四、实战示例 数据准备五、连接的性
阅读更多...
使用Python实现IP地址和端口状态检测与监控

使用Python实现IP地址和端口状态检测与监控

《使用Python实现IP地址和端口状态检测与监控》在网络运维和服务器管理中,IP地址和端口的可用性监控是保障业务连续性的基础需求,本文将带你用Python从零打造一个高可用IP监控系统,感兴趣的小伙... 目录概述：为什么需要IP监控系统使用步骤说明1. 环境准备2. 系统部署3. 核心功能配置系统效果展
阅读更多...
MySQL中的分组和多表连接详解

MySQL中的分组和多表连接详解

《MySQL中的分组和多表连接详解》：本文主要介绍MySQL中的分组和多表连接的相关操作,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录mysql中的分组和多表连接一、MySQL的分组（group javascriptby ）二、多表连接（表连接会产生大量的数据垃圾）MySQL中的
阅读更多...
使用Java将各种数据写入Excel表格的操作示例

使用Java将各种数据写入Excel表格的操作示例

《使用Java将各种数据写入Excel表格的操作示例》在数据处理与管理领域,Excel凭借其强大的功能和广泛的应用,成为了数据存储与展示的重要工具,在Java开发过程中,常常需要将不同类型的数据,本文... 目录前言安装免费Java库1. 写入文本、或数值到 Excel单元格2. 写入数组到 Excel表格
阅读更多...
python处理带有时区的日期和时间数据

python处理带有时区的日期和时间数据

《python处理带有时区的日期和时间数据》这篇文章主要为大家详细介绍了如何在Python中使用pytz库处理时区信息,包括获取当前UTC时间,转换为特定时区等,有需要的小伙伴可以参考一下... 目录时区基本信息python datetime使用timezonepandas处理时区数据知识延展时区基本信息
阅读更多...
Qt实现网络数据解析的方法总结

Qt实现网络数据解析的方法总结

《Qt实现网络数据解析的方法总结》在Qt中解析网络数据通常涉及接收原始字节流,并将其转换为有意义的应用层数据,这篇文章为大家介绍了详细步骤和示例,感兴趣的小伙伴可以了解下... 目录1. 网络数据接收2. 缓冲区管理（处理粘包/拆包）3. 常见数据格式解析3.1 jsON解析3.2 XML解析3.3 自定义
阅读更多...
SpringMVC 通过ajax 前后端数据交互的实现方法

SpringMVC 通过ajax 前后端数据交互的实现方法

《SpringMVC通过ajax前后端数据交互的实现方法》：本文主要介绍SpringMVC通过ajax前后端数据交互的实现方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价... 在前端的开发过程中，经常在html页面通过AJAX进行前后端数据的交互，SpringMVC的controll
阅读更多...
MySQL 中的 JSON 查询案例详解

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》：本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J
阅读更多...
Pandas统计每行数据中的空值的方法示例

Pandas统计每行数据中的空值的方法示例

《Pandas统计每行数据中的空值的方法示例》处理缺失数据（NaN值）是一个非常常见的问题,本文主要介绍了Pandas统计每行数据中的空值的方法示例,具有一定的参考价值,感兴趣的可以了解一下... 目录什么是空值？为什么要统计空值？准备工作创建示例数据统计每行空值数量进一步分析www.chinasem.cn处
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2