[推荐] SQL SERVER 2008 更改跟踪 60ldslove]

发布时间:2016-12-6 8:54:19 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"[推荐] SQL SERVER 2008 更改跟踪 60ldslove]",主要涉及到[推荐] SQL SERVER 2008 更改跟踪 60ldslove]方面的内容,对于[推荐] SQL SERVER 2008 更改跟踪 60ldslove]感兴趣的同学可以参考一下。

SQL SERVER 2008 更改跟踪 /* 看书,看联机期间。。。顺带把看到的写下 与SQL SERVER 2008 CDC 异步捕获数据变更的不同,更改跟踪是同步进程, 是DML(INSERT/UPDATE/DELETE)事务的一部分,它可以使用最小的C盘存储 开销来侦测数据行的净变更.那么它也就不能像CDC那样可以提供用户表的 历史更改信息. 更改是使用异步进程捕获的,此进程读取事务日志,并 且对系统造成的影响很小. 更改跟踪捕获更改了表行这一事实,但不会捕获更改的数据.这样,应用程 序就可以确定使用从用户表中直接获取的最新行数据更改的行.因此,与变 更数据捕获相比,更改跟踪可以解答的历史问题比较有限.但是,对于不需 要历史信息的那些应用程序,更改跟踪产生的存储开销要小得多,因为它不 需要捕获更改的数据(不需要触发器和表时间戳).它使用同步跟踪机制来 跟踪更改.此功能旨在最大限度地减少 DML 操作开销. 总的来说有以下几点: 1 减少了开发时间: 由于 SQL Server 2008 中提供了更改跟踪功能,因此无需开发自定义解决方案.   2 不需要架构更改: 使用更改跟踪不需要执行以下任务:添加列;添加触发器;如果无法将列添加到用   户表,则需要创建要在其中跟踪已删除的行或存储更改跟踪信息的端表. 3 内置清除机制: 更改跟踪的清除操作在后台自动执行.不需要端表中存储的数据的自定义清除.   4 提供更改跟踪功能的目的是获取更改信息: 使用更改跟踪功能可使信息查询和使用更方便.列跟踪 记录提供与更改的数据相关的详细信息.   5 降低了 DML 操作的开销: 同步更改跟踪始终会有一些开销.但是,使用更改跟踪有助于使开销最小  化.开销通常会低于使用其他解决方案,对于需要使用触发器的解决方案  ,尤其如此.   6 更改跟踪是基于提交的事务进行的: 更改的顺序基于事务提交时间.在存在长时间运行和重叠事务的   情况下,这样可获得可靠的结果.必须专门设计使用 timestamp    值的自定义解决方案,以处理这些情况.   7 配置和管理更改跟踪的标准工具: SQL Server 2008 提供标准的 DDL 语句、SQL Server Management  Studio、目录视图和安全权限. */ /* 建立测试数据库 */ IF NOT EXISTS (SELECT NAME FROM SYS.databases WHERE name='CHANGE_TRACK_DB') BEGIN CREATE DATABASE CHANGE_TRACK_DB END /* 要启用数据库更改跟踪功能,需要配置CHANGE_TRACKING数据库选项.也可以配置跟踪的数据在 数据库保留多久,以及是否启用自动清除.配置保留期将会影响到需要维护的跟踪数据的大小. 该值过高可能会影响存储.太低的话在远程应用程序同步不够的情况下,会引发通另一应用程序 的同步问题. 配置更改跟踪 */ ALTER DATABASE CHANGE_TRACK_DB SET CHANGE_TRACKING= ON (CHANGE_RETENTION=36 HOURS,  AUTO_CLEANUP=ON) /* 使用更改跟踪时的最佳实践是为数据库启用快照隔离.不使用快照隔离会引发事务不一致的 变更信息.对有显著DML活动的数据库和表,以一致的方式捕获更改跟踪的信息很重要(抓取最 新版本并使用该版本号来获取适当的数据) 由于行版本的生成,启用快照隔离会在tempdb中增加额外的使用空间.会带来I/O开销的增加. 启用快照隔离 */ ALTER DATABASE CHANGE_TRACK_DB SET ALLOW_SNAPSHOT_ISOLATION ON GO /* 通过查询sys.change_tracking_databases来确认数据库是否以正确启用更改跟踪. */ SELECT  DB_NAME(DATABASE_ID) AS [DB_NAME] ,IS_AUTO_CLEANUP_ON ,RETENTION_PERIOD ,RETENTION_PERIOD_UNITS_DESC FROM sys.change_tracking_databases /*结果 DB_NAME IS_AUTO_CLEANUP_ON RETENTION_PERIOD RETENTION_PERIOD_UNITS_DESC --------------- ------------------ ---------------- ---------------------------- CHANGE_TRACK_DB 1                  36               HOURS (1 行受影响) */ /* 创建测试表 */ USE CHANGE_TRACK_DB GO CREATE TABLE CHANGE_TRACKING_USER (USERID  INT NOT NULL PRIMARY KEY  IDENTITY(1,1) ,NAME VARCHAR(20) NOT NULL ,ADDRESS  VARCHAR(100) NOT NULL) /* 对于要打开更改跟踪以及要跟踪哪些列被跟新了的表,需要打开表的CHANGE_TRACKING选项 和TRACK_COLUMNS_UPDATED选项. */ ALTER TABLE CHANGE_TRACKING_USER ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED= ON) /* 查询sys.change_tracking_tables目录视图可以获得启用跟踪更改的详细信息. */ SELECT  OBJECT_NAME(OBJECT_ID) AS [TB_NAME] ,IS_TRACK_COLUMNS_UPDATED_ON FROM sys.change_tracking_tables /*结果 TB_NAME IS_TRACK_COLUMNS_UPDATED_ON -------------------- --------------------------- CHANGE_TRACKING_USER 1 (1 行受影响) */ /* 对表进行插入数据来捕获更改跟踪. */ INSERT CHANGE_TRACKING_USER(NAME,ADDRESS) VALUES ('香蕉','dss省fdfd市'), ('鸽子','山东省青岛市'), ('水哥','江苏省苏州市'), ('土豆','XX省XX市'); /* 用来查看正在同步的是一个函数CHANGE_TRACKING_CURRENT_VERSION(),返回的是最后提交的事务的版本号 .所有发生在启用更改跟踪表中的DML操作都会照成版本号的增长.版本号用来确定更改. */ SELECT CHANGE_TRACKING_CURRENT_VERSION() /*结果   -------------------- 1 (1 行受影响) */ /* 函数CHANGE_TRACKING_MIN_VALID_VERSION()可以获得表的最小可用版本号.如果断开连接的程序不同步的时 间超过了更改跟踪保留期限.那么就要对应用程序的数据进行彻底的刷新. */ SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('CHANGE_TRACKING_USER')) /*结果 -------------------- 0 (1 行受影响) */ /* 对于更改的侦测我们可以用函数CHANGETABLE.该函数有2种用法:使用CHANGES关键字来检测从指定的同步 版本以来发生的更改;或者使用VERSION关键字来返回最新的更改跟踪版本. */ SELECT USERID --返回的是主键 ,SYS_CHANGE_OPERATION --I 代表INSERT,U代表UPDATE,D代表DELETE ,SYS_CHANGE_VERSION --返回的是版本号,因为这4条数据是在同一个INSERT中添加的 --,所以下面的结果版本号相同 --以下的函数返回的是自版本0以来的更改.第一个参数是表名称 FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER,0) A  /*结果 USERID      SYS_CHANGE_OPERATION SYS_CHANGE_VERSION ----------- -------------------- -------------------- 1           I                    1 2           I                    1 3           I                    1 4           I                    1 (4 行受影响) */ /* 当收集同步信息时,使用SET TRANSACTION ISOLATION LEVEL SNAPSHOT 和BEGIN TRAN..COMMIT TRAN 来封装收集的更改信息和相关的当前更改跟踪版本以及最小的可用版本.使用快照隔离允许更改跟踪 的数据具有事务一致性的形式. */ UPDATE CHANGE_TRACKING_USER SET NAME='柯少' WHERE USERID=1 UPDATE CHANGE_TRACKING_USER SET ADDRESS='火星' WHERE USERID=4 DELETE FROM CHANGE_TRACKING_USER WHERE USERID=2 --检查最新的版本号 SELECT CHANGE_TRACKING_CURRENT_VERSION()  /*结果 -------------------- 4 (1 行受影响) */ /* 当程序收集了自数据版本1后的数据.下面可以检测自版本1起发生的所有更改 */ SELECT USERID ,SYS_CHANGE_VERSION ,SYS_CHANGE_OPERATION ,SYS_CHANGE_COLUMNS FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER,1) AS T /*结果 USERID      SYS_CHANGE_VERSION   SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS ----------- -------------------- -------------------- ------------------- 1           2                    U                    0x0000000002000000 2           4                    D                    NULL 4           3                    U                    0x0000000003000000 (3 行受影响) SYS_CHANGE_COLUMNS列式包含从最新版本开始更新过的列的VARBINARY值,可以使用 CHANGE_TRACKING_IS_COLUMN_IN_MASK函数来解释它.该函数接受2个参数:表的列ID和VARBINARY 值. 下面使用这个函数来检查NAME列和ADDRESS列是否被修改过. */ SELECT USERID ,CHANGE_TRACKING_IS_COLUMN_IN_MASK( COLUMNPROPERTY(OBJECT_ID('CHANGE_TRACKING_USER'),'NAME','COLUMNID')--该函数返回对应的列ID ,SYS_CHANGE_COLUMNS) NAME_IS_CHANGED ,CHANGE_TRACKING_IS_COLUMN_IN_MASK( COLUMNPROPERTY(OBJECT_ID('CHANGE_TRACKING_USER'),'ADDRESS','COLUMNID') ,SYS_CHANGE_COLUMNS) ADDRESS_IS_CHANGED FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER,1) AS T WHERE SYS_CHANGE_OPERATION='U'--确定修改的列 /*结果 USERID      NAME_IS_CHANGED ADDRESS_IS_CHANGED ----------- --------------- ------------------ 1           1               0 4           0               1 (2 行受影响) */ /* CHANGETABLE 通过VERSION 参数来返回最新的版本. */ SELECT A.USERID ,NAME ,ADDRESS ,SYS_CHANGE_VERSION FROM CHANGE_TRACKING_USER A CROSS APPLY CHANGETABLE(VERSION CHANGE_TRACKING_USER,(USERID),(A.USERID))  T /* USERID      NAME ADDRESS SYS_CHANGE_VERSION ----------- ------- --------------- -------------------- 1           柯少 dss省fdfd市 2 3           水哥 江苏省苏州市 1 4           土豆 火星 3 (3 行受影响) 下面再演示一个UPDATE来演示版本的不同. */ UPDATE CHANGE_TRACKING_USER SET ADDRESS='冥王星' WHERE USERID=3 SELECT CHANGE_TRACKING_CURRENT_VERSION() --检查最新的版本号 /* -------------------- 5 (1 行受影响) */ SELECT A.USERID ,NAME ,ADDRESS ,SYS_CHANGE_VERSION FROM CHANGE_TRACKING_USER A CROSS APPLY CHANGETABLE(VERSION CHANGE_TRACKING_USER,(USERID),(A.USERID))  T /* USERID      NAME                 ADDRESS SYS_CHANGE_VERSION ----------- -------------------- ------------- -------------------- 1           柯少                   dss省fdfd市 2 3           水哥                   冥王星 5 4           土豆                   火星 3 (3 行受影响) 可以看到USERID=3的版本号为5,这是因为版本号是一致递增的,上面执行过如下代码: UPDATE CHANGE_TRACKING_USER SET NAME='柯少' WHERE USERID=1 UPDATE CHANGE_TRACKING_USER SET ADDRESS='火星' WHERE USERID=4 DELETE FROM CHANGE_TRACKING_USER WHERE USERID=2 检查最新的版本号 SELECT CHANGE_TRACKING_CURRENT_VERSION()  /*结果 -------------------- 4 (1 行受影响) */ 所以现在最新的版本号位5.没有修改的行版本号不变. */ /* 最后测试如何通过DML操作提供更改跟踪应用程序上下文信息,可以确定是哪一应用程序对那些行 进行了数据修改.它的作用是如果有多个应用程序对数据源进行数据同步,这将会是有用的信息. 使用CHANGE_TRACKING_CONTEXT函数来查询,函数只有一个输入参数CONTEXT,它是VARBINARY数据 类型. 首先要保存上下文信息的变量,然后在CHANGE_TRACKING_CONTEXT函数中使用变量,再向更改跟踪表 中插入一条新行 */ DECLARE @CONTEXT VARBINARY(128)=CAST('DS_ALEX' AS VARBINARY(128)) ;WITH CHANGE_TRACKING_CONTEXT(@CONTEXT) INSERT CHANGE_TRACKING_USER(NAME,ADDRESS) VALUES ('ALEX','水星')  --现在查询从版本5发生的所有更改. SELECT USERID ,SYS_CHANGE_OPERATION ,SYS_CHANGE_VERSION ,CAST(SYS_CHANGE_CONTEXT AS VARCHAR(50)) AppContext FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER,5) AS T /* 结果 USERID      SYS_CHANGE_OPERATION SYS_CHANGE_VERSION   AppContext ----------- -------------------- -------------------- ------------ 5           I                    6                    DS_ALEX (1 行受影响) */ /* 到此结束! 以上主要涉及了建库建表, ALTER DATABASE ....ENABLE CHANGE_TRACKING. 启用数据库更改跟踪 CHANGE_RETENTION 和 AUTO_CLEANUP 指定更改跟踪保留期限和自动清除. 查询SYS.CHANGE_TRACKING_DATABASES目录视图检查数据库更改跟踪的状态. ALTER TABLE ...ENABLE CHANGE_TRACKING  TRACK_COLUMNS_UPDATED 指定列级别更改也会被跟踪. SYS.CHANGE_TRACKING_TABLES目录视图确认表的更改跟踪状态 一些检测更改跟踪数据的不同函数: CHANGE_TRACKING_CURRENT_VERSION() 返回最后提交的事务版本号. CHANGE_TRACKING_MIN_VALID_VERSION() 返回更改跟踪表的最小可用版本号 CHANGETABLE :VERSION 返回最新的更改版本  CHANGES 检测自指定同步版本以来的更改 CHANGE_TRACKING_IS_COLUMN_IN_MASK 检测更改跟踪表中那些列被更新 CHANGE_TRACKING_CONTEXT 通过DML操作存储更改上下文,从而可以跟踪哪一应用程序 修改了什么数据. */ 博文地址:http://blog.csdn.net/ldslove/archive/2010/05/21/5615010.aspx

东升辛苦了

该回复于2010-07-28 09:05:45被版主删除

看看...学习下

顶....again

ding

在给东升顶下 吃饭去哦

看看...学习下

学习了

引用 1 楼 htl258 的回复:东升辛苦了 是啊

看不懂

使劲儿的飘过,咱分状态

学习一下。

该回复于2010-07-28 14:21:12被版主删除

一想二干三成功,一等二看三成空!      成功者之所以成功,就是在大家都犹豫的时候,而他们却付出了行动!      朋友们,不要再等了,也不要再看了!赶快和我们一起加入最时尚地soho一族吧!      你要知道,你荒废的今日,就是昨日逝去之人所期盼的明日!            如果看好的话,就加 QQ:38806306 这里,只允许你成功!这里,不允许你失败!!!                       ---- 送给正在观望的朋友们。。。   

bu cuo !

晕啊,看不懂啊。。。。。。。

接分!

帮顶。。

kankan

接分了

收藏下。

丫又只放60分

学习...学习...值得一学>>

搂主辛苦了 更改跟踪用起来还是太麻烦了,需要些这么长

我们还在弄2005的,学校啊慢啊!!

不错,看了,学到了

顶,就是我不太懂

看不懂,

学习中哦 现在我的知识还太少了 进来学习一下咯 值得分享 谢谢 顶一下

很好啊

很强大,很好

很强大,很好 1

学习...学习...值得一学

顶~!

学习学习,虽然看不懂,顺便拿点积分

楼主辛苦了!我们就跟踪你了!

lz辛苦了,收藏~

看到了,学习了!

太高深了……

学习学习

ding 

谢谢!!!

顶一个

aaaa

学习中....

学习了

收藏了,回去好好学习!

赚积分

学习下。。。。。

很好,谢谢

很好,谢谢

增加长见识中

增加长见识了,谢谢!

路过,路过,学习

好长啊 楼主辛苦了

路过。。。学习。。。。 惠佳网络--专业提供域名注册、虚拟主机服务 http://www.hjweb.net

顶一个

顶,好东西

路过 up

好呀  学习学习

很好,学习了,谢谢

xuexi...

谢谢,学习了

dddddddddddddddddd

收藏了

08版的比05版的增加了跟踪功能 并启用快照功能 o(∩_∩)o... 很好用哦

学习一下,哈

路过,路过,学习了

学习.

学习ING

学习……

学习一下

ALTER DATABASE CHANGE_TRACK_DB   SET CHANGE_TRACKING = on 这个是08的新产品吧,我在05上做运行报错,奶奶的

帮顶。。

晕了!!!

Mark,学习了

学习了

学习中

不错,好好学习下

引用 59 楼 javajiao 的回复:路过 up测试。。。。。

搞数据库搞到这样,估计差不多了!

学习,学习

我要好好学习

定期!

bucuo

为什么我装不了2008

数据库简单

给东升哥顶起来

学习学习。。。。

学习了,呵呵

哈哈,学习一下喽!

超详细,超好!

有时间测试下

顶!!!

好高的楼,。。,

支持啊呵呵

支持,看看如何,

学习了,ding

路过,路过,学习

liao jie

上一篇:mysql表拆分 40分,无满意结帖,结帖人programs_net]
下一篇:像世纪佳缘、百合网这类婚介网站用什么方法来实现对用户的搜索? 20分,无满意结帖,结帖人songruqi]

相关文章

相关评论