SQL SERVER 2008 开发系列（四）

公告

Posted on 2008-09-16 20:31 狂笑人生阅读(273) 评论(0) 收藏举报

Change Tracking 更改跟踪（新特点）

/**********************************************************
第一章: 概述
**********************************************************/

经常会有需求记录用户表中由DML操作(Insert/Updae/Delete)引起的数据变化，在SQL Server 2008 以前的版本中，要实现这样的功能只能通过Trigger或者数据比对(例如SCD处理等)，而且必须针对每个用户表开发。SQL Server 2008中新增了两种记录数据变化的功能：

1、Change Tracking

2、Change Data Capture(CDC)

本章主要介绍Change Tracking 更改跟踪功能；

CDC功能介绍请参照《SQL SERVER 2008 开发系列（七）》

Change Tracking

Change Tracking仅记录DML操作的发生时间、类型和影响到的字段，不包含具体的变化数值，客户端通过传传递上次同步的版本号来获取从上次同步到现在的变化记录。对于应用数据缓存的场合，这个功能非常实用，可以每隔一定时间获取数据表中的变化记录，然后根据变化记录中的主键来获取更新过的数据从而刷新缓存。

Change Tracking通过对要执行的DML语句的分析获取变化记录，而不是去读取日志。DML语句提交执行时Change Tracking便已可用，而不需要等待DML完成后事务日志写入时才可用，因此Change Tracking的响应比Change Data Capture要快。

Change Tracking记录的数据比Change Data Capture少，对服务器性能的影响也小。

下图来自于SQL Server Books Online:

/**********************************************************
第二章: 何如开启更改跟踪
**********************************************************/

--搭建演示环境
USE master
GO
--创建数据库
--CREATE DATABASE Blog
--GO

ALTER DATABASE Blog
SET Change_Tracking = ON --开启更改跟踪功能
(CHANGE_RETENTION = 2 MINUTES, AUTO_CLEANUP = ON)
GO

--创建更改跟踪演示表
USE Blog
GO
CREATE TABLE TestCT
(
    ID int PRIMARY KEY,
    Name varchar(50),
    Description varchar(200)
)
GO
--更改表属性，开启更改跟踪功能
ALTER TABLE TestCT
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
GO

--通过系统表sys.change_tracking_tables 获取开启更改跟踪的数据表列表
SELECT OBJECT_NAME(object_id) AS table_name, sys.change_tracking_tables.* FROM sys.change_tracking_tables
GO

/**********************************************************
第三章: 如何获取数据表更改信息
**********************************************************/

--得到当前版本号，初始时为0
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS '当前版本号'

--插入两条测试数据
INSERT INTO TestCT VALUES (1, 'ABC', NULL)
INSERT INTO TestCT VALUES (2, 'XYZ', NULL)

--得到当前版本号，初始时为2
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS '当前版本号'

--利用CHANGETABLE函数获取数据表的更改信息
--CHANGETABLE(参数一：开启更改的数据表名称，参数二：版本号）返回数据表
SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT

--在一个事务中插入两条数据，我们比较这次得到的版本号
BEGIN TRAN
INSERT INTO TestCT VALUES (3, 'CBA', NULL)
INSERT INTO TestCT VALUES (4, 'ZYX', NULL)
COMMIT
--同一个事务中的多条T-SQL操作只产生一个版本信息
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS '当前版本号'

--查看更改版本2以后的信息
SELECT * FROM CHANGETABLE(CHANGES TestCT, 2) CT

--更新数据表的非键列，此时只产生一个更新版本
UPDATE TestCT SET Name = 'abc' WHERE ID = 1
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS '当前版本号'

--查看版本信息及更改操作
--相对初始版本，更改操作（SYS_CHANGE_OPERATION) 是I-插入
SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT
--相对于版本3，更改操作（SYS_CHANGE_OPERATION) 是U-更新
SELECT * FROM CHANGETABLE(CHANGES TestCT, 3) CT

--利用CHANGE_TRACKING_IS_COLUMN_IN_MASK函数，我们可以知道是哪些列进行更改
SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (
    COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Name', 'ColumnId') --获得列属性，列ID
    ,
    SYS_CHANGE_COLUMNS
    ) AS is_column_Name_changed,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (
    COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Description', 'ColumnId')
    ,
    SYS_CHANGE_COLUMNS
    ) AS is_column_Description_changed
    FROM CHANGETABLE(CHANGES TestCT, 3) CT

--更新主键操作，因为更新主键操作原理：先删除，然后新增，因此会产生两个版本号
UPDATE TestCT SET ID = 5 WHERE ID = 1

--更新版本信息
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS '当前版本号'

SELECT * FROM CHANGETABLE(CHANGES TestCT, 3) CT

--删除操作
DELETE TestCT WHERE ID = 5

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS '当前版本号'

SELECT * FROM CHANGETABLE(CHANGES TestCT, 5) CT

--返回指定行的最新更改跟踪信息
SELECT * FROM CHANGETABLE(VERSION TestCT, (ID), (4)) CT

--获取更改文本信息
--定义更改提醒内容
DECLARE @context AS varbinary(128);
SET @context = CAST('描述被修改成NA' AS varbinary(128));
WITH CHANGE_TRACKING_CONTEXT (@context)
UPDATE TestCT SET Description = 'NA';
--查看版本信息表中更改文本内容
SELECT CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION,
CAST(SYS_CHANGE_CONTEXT AS varchar(20)) AS change_context
FROM CHANGETABLE(CHANGES TestCT, 6) AS CT
select * from TestCT

--获取指定数据表的最小更改版本号
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('TestCT')) AS min_valid_version;
--清空表数据
TRUNCATE TABLE TestCT;
--获取指定数据表的最小更改版本号
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('TestCT')) AS min_valid_version;

--插入数据后,版本号从最小版本号开始
INSERT INTO TestCT VALUES (1, 'ABC', NULL)

SELECT * FROM CHANGETABLE(CHANGES TestCT, 0) CT
SELECT * FROM CHANGETABLE(CHANGES TestCT, 13) CT

/**********************************************************
第四章：数据定义语言与更改跟踪
***********************************************************/

--以下数据表结构更改，新增一列被监测
ALTER TABLE TestCT ADD Age int;
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS '当前版本号'
--更新列数据
UPDATE TestCT SET Age = 25;

--获取数据表列更改情况
SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (
    COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Name', 'ColumnId')
    ,
    SYS_CHANGE_COLUMNS
    )
    AS is_column_Name_changed,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (
    COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Description', 'ColumnId')
    ,
    SYS_CHANGE_COLUMNS
    )
    AS is_column_Description_changed,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (
    COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Age', 'ColumnId')
    ,
    SYS_CHANGE_COLUMNS
    )
    AS is_column_Age_changed
    FROM CHANGETABLE(CHANGES TestCT, 7) CT;

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS '当前版本号'

--删除一列
ALTER TABLE TestCT DROP COLUMN Age
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS '当前版本号'

/**********************************************************
第五章：总结
***********************************************************/

SQL SERVER 2008 提供了数据表的更改跟踪，更加方便了数据的同步和更新；这对数据仓库的数据更新提供了更加方便，简单的操作。

刷新页面返回顶部