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开发一个Ditto剪贴板数据导出工具

使用Python开发一个Ditto剪贴板数据导出工具

《使用Python开发一个Ditto剪贴板数据导出工具》在日常工作中,我们经常需要处理大量的剪贴板数据,下面将介绍如何使用Python的wxPython库开发一个图形化工具,实现从Ditto数据库中读... 目录前言运行结果项目需求分析技术选型核心功能实现1. Ditto数据库结构分析2. 数据库自动定位3
阅读更多...
pandas数据的合并concat()和merge()方式

pandas数据的合并concat()和merge()方式

《pandas数据的合并concat()和merge()方式》Pandas中concat沿轴合并数据框（行或列）,merge基于键连接（内/外/左/右）,concat用于纵向或横向拼接,merge用于... 目录concat() 轴向连接合并(1) join='outer'，axis=0(2)join='o
阅读更多...
批量导入txt数据到的redis过程

批量导入txt数据到的redis过程

《批量导入txt数据到的redis过程》用户通过将Redis命令逐行写入txt文件,利用管道模式运行客户端,成功执行批量删除以Product*匹配的Key操作,提高了数据清理效率... 目录批量导入txt数据到Redisjs把redis命令按一条 一行写到txt中管道命令运行redis客户端成功了批量删除k
阅读更多...
MySQL中EXISTS与IN用法使用与对比分析

MySQL中EXISTS与IN用法使用与对比分析

《MySQL中EXISTS与IN用法使用与对比分析》在MySQL中,EXISTS和IN都用于子查询中根据另一个查询的结果来过滤主查询的记录,本文将基于工作原理、效率和应用场景进行全面对比... 目录一、基本用法详解1. IN 运算符2. EXISTS 运算符二、EXISTS 与 IN 的选择策略三、性能对比
阅读更多...
MySQL常用字符串函数示例和场景介绍

MySQL常用字符串函数示例和场景介绍

《MySQL常用字符串函数示例和场景介绍》MySQL提供了丰富的字符串函数帮助我们高效地对字符串进行处理、转换和分析,本文我将全面且深入地介绍MySQL常用的字符串函数,并结合具体示例和场景,帮你熟练... 目录一、字符串函数概述1.1 字符串函数的作用1.2 字符串函数分类二、字符串长度与统计函数2.1
阅读更多...
SpringBoot多环境配置数据读取方式

SpringBoot多环境配置数据读取方式

《SpringBoot多环境配置数据读取方式》SpringBoot通过环境隔离机制,支持properties/yaml/yml多格式配置,结合@Value、Environment和@Configura... 目录一、多环境配置的核心思路二、3种配置文件格式详解2.1 properties格式（传统格式）1.
阅读更多...
SQL Server跟踪自动统计信息更新实战指南

SQL Server跟踪自动统计信息更新实战指南

《SQLServer跟踪自动统计信息更新实战指南》本文详解SQLServer自动统计信息更新的跟踪方法,推荐使用扩展事件实时捕获更新操作及详细信息,同时结合系统视图快速检查统计信息状态,重点强调修... 目录SQL Server 如何跟踪自动统计信息更新：深入解析与实战指南 核心跟踪方法1️⃣ 利用系统目录
阅读更多...
MySQL 内存使用率常用分析语句

MySQL 内存使用率常用分析语句

《MySQL内存使用率常用分析语句》用户整理了MySQL内存占用过高的分析方法,涵盖操作系统层确认及数据库层bufferpool、内存模块差值、线程状态、performance_schema性能数据... 目录一、 OS层二、 DB层1. 全局情况2. 内存占js用详情最近连续遇到mysql内存占用过高导致
阅读更多...
解决pandas无法读取csv文件数据的问题

解决pandas无法读取csv文件数据的问题

《解决pandas无法读取csv文件数据的问题》本文讲述作者用Pandas读取CSV文件时因参数设置不当导致数据错位,通过调整delimiter和on_bad_lines参数最终解决问题,并强调正确参... 目录一、前言二、问题复现1. 问题2. 通过 on_bad_lines=‘warn’ 跳过异常数据3
阅读更多...
Mysql中设计数据表的过程解析

Mysql中设计数据表的过程解析

《Mysql中设计数据表的过程解析》数据库约束通过NOTNULL、UNIQUE、DEFAULT、主键和外键等规则保障数据完整性,自动校验数据,减少人工错误,提升数据一致性和业务逻辑严谨性,本文介绍My... 目录1.引言2.NOT NULL——制定某列不可以存储NULL值2.UNIQUE——保证某一列的每一
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2