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。

相关文章

SQL BETWEEN 语句的基本用法详解

SQL BETWEEN 语句的基本用法详解

《SQLBETWEEN语句的基本用法详解》SQLBETWEEN语句是一个用于在SQL查询中指定查询条件的重要工具,它允许用户指定一个范围,用于筛选符合特定条件的记录,本文将详细介绍BETWEEN语... 目录概述BETWEEN 语句的基本用法BETWEEN 语句的示例示例 1：查询年龄在 20 到 30 岁
阅读更多...
MySQL DQL从入门到精通

MySQL DQL从入门到精通

《MySQLDQL从入门到精通》通过DQL,我们可以从数据库中检索出所需的数据,进行各种复杂的数据分析和处理,本文将深入探讨MySQLDQL的各个方面,帮助你全面掌握这一重要技能,感兴趣的朋友跟随小... 目录一、DQL 基础：SELECT 语句入门二、数据过滤：WHERE 子句的使用三、结果排序：ORDE
阅读更多...
MySQL MCP 服务器安装配置最佳实践

MySQL MCP 服务器安装配置最佳实践

《MySQLMCP服务器安装配置最佳实践》本文介绍MySQLMCP服务器的安装配置方法,本文结合实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下... 目录mysql MCP 服务器安装配置指南简介功能特点安装方法数据库配置使用MCP Inspector进行调试开发指
阅读更多...
mysql中insert into的基本用法和一些示例

mysql中insert into的基本用法和一些示例

《mysql中insertinto的基本用法和一些示例》INSERTINTO用于向MySQL表插入新行,支持单行/多行及部分列插入,下面给大家介绍mysql中insertinto的基本用法和一些示例... 目录基本语法插入单行数据插入多行数据插入部分列的数据插入默认值注意事项在mysql中，INSERT I
阅读更多...
一文详解MySQL如何设置自动备份任务

一文详解MySQL如何设置自动备份任务

《一文详解MySQL如何设置自动备份任务》设置自动备份任务可以确保你的数据库定期备份,防止数据丢失,下面我们就来详细介绍一下如何使用Bash脚本和Cron任务在Linux系统上设置MySQL数据库的自... 目录1. 编写备份脚本1.1 创建并编辑备份脚本1.2 给予脚本执行权限2. 设置 Cron 任务2
阅读更多...
SQL Server修改数据库名及物理数据文件名操作步骤

SQL Server修改数据库名及物理数据文件名操作步骤

《SQLServer修改数据库名及物理数据文件名操作步骤》在SQLServer中重命名数据库是一个常见的操作,但需要确保用户具有足够的权限来执行此操作,：本文主要介绍SQLServer修改数据... 目录一、背景介绍二、操作步骤2.1 设置为单用户模式（断开连接）2.2 修改数据库名称2.3 查找逻辑文件名
阅读更多...
SQL Server数据库死锁处理超详细攻略

SQL Server数据库死锁处理超详细攻略

《SQLServer数据库死锁处理超详细攻略》SQLServer作为主流数据库管理系统,在高并发场景下可能面临死锁问题,影响系统性能和稳定性,这篇文章主要给大家介绍了关于SQLServer数据库死... 目录一、引言二、查询 Sqlserver 中造成死锁的 SPID三、用内置函数查询执行信息1. sp_w
阅读更多...
canal实现mysql数据同步的详细过程

canal实现mysql数据同步的详细过程

《canal实现mysql数据同步的详细过程》：本文主要介绍canal实现mysql数据同步的详细过程,本文通过实例图文相结合给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的... 目录1、canal下载2、mysql同步用户创建和授权3、canal admin安装和启动4、canal
阅读更多...
SQL中JOIN操作的条件使用总结与实践

SQL中JOIN操作的条件使用总结与实践

《SQL中JOIN操作的条件使用总结与实践》在SQL查询中,JOIN操作是多表关联的核心工具,本文将从原理,场景和最佳实践三个方面总结JOIN条件的使用规则,希望可以帮助开发者精准控制查询逻辑... 目录一、ON与WHERE的本质区别二、场景化条件使用规则三、最佳实践建议1.优先使用ON条件2.WHERE用
阅读更多...
MySQL存储过程之循环遍历查询的结果集详解

MySQL存储过程之循环遍历查询的结果集详解

《MySQL存储过程之循环遍历查询的结果集详解》：本文主要介绍MySQL存储过程之循环遍历查询的结果集,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言1. 表结构2. 存储过程3. 关于存储过程的SQL补充总结前言近来碰到这样一个问题：在生产上导入的数据发现
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2