Chinaunix首页 | 论坛 | 博客
  • 博客访问: 310129
  • 博文数量: 163
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: -40
  • 用 户 组: 普通用户
  • 注册时间: 2017-03-08 00:28
文章分类

全部博文(163)

文章存档

2015年(2)

2014年(35)

2013年(28)

2012年(30)

2011年(22)

2010年(14)

2009年(8)

2008年(13)

2007年(11)

分类: 数据库开发技术

2012-10-10 14:34:23

SQL Server 2008中SQL应用系列--目录索引

  本文主要介绍SQL Server中记录数据变更的四个方法:触发器、Output子句、变更数据捕获(Change Data Capture 即CDC)功能、同步更改跟踪。其中后两个为SQL Server 2008所新增。

一、触发器

  在SQL Server的早期版本中,如果要记录某个表或视图的Insert/Update/Delete操作,我们可以借助触发器(Trigger)(),这在数据量较小的情况下往往是有效的方式之一,其中后触发器(After Trigger)只能跟踪表的三个操作中的任意组合,而前触发器(Instead Of trigger)可以处理表和视图的更新(即使普通的Update View语句在某些列不明确的情况下报错)。我们看两个例子:

  准备基础数据:

  1. USE testDb2
  2. GO
  3. --创建两个测试表
  4. IF NOT OBJECT_ID('DepartDemo') IS NULL
  5. DROP TABLE [DepartDemo]
  6. GO
  7. IF NOT OBJECT_ID('DepartChangeLogs') IS NULL
  8. DROP TABLE [DepartChangeLogs]
  9. GO
  10. --测试表
  11. CREATE TABLE [dbo].[DepartDemo](
  12. [DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY,
  13. [DName] [nvarchar](200) NULL,
  14. [DCode] [nvarchar](500) NULL,
  15. [Manager] [nvarchar](50) NULL,
  16. [ParentID] [int] NOT NULL DEFAULT ((0)),
  17. [AddUser] [nvarchar](50) NULL,
  18. [AddTime] [datetime] NULL,
  19. [ModUser] [nvarchar](50) NULL,
  20. [ModTime] [datetime] NULL,
  21. [CurState] [smallint] NOT NULL DEFAULT ((0)),
  22. [Remark] [nvarchar](500) NULL,
  23. [F1] [int] NOT NULL DEFAULT ((0)),
  24. [F2] [nvarchar](300) NULL
  25. )
  26. GO
  27. --记录日志表
  28. CREATE TABLE [DepartChangeLogs]
  29. ([LogID] [bigint] IDENTITY(1001,1) NOT NULL PRIMARY KEY,
  30. [DID] [int] NOT NULL,
  31. [DName] [nvarchar](200) NULL,
  32. [DCode] [nvarchar](500) NULL,
  33. [Manager] [nvarchar](50) NULL,
  34. [ParentID] [int] NOT NULL DEFAULT ((0)),
  35. [AddUser] [nvarchar](50) NULL,
  36. [AddTime] [datetime] NULL,
  37. [ModUser] [nvarchar](50) NULL,
  38. [ModTime] [datetime] NULL,
  39. [CurState] [smallint] NOT NULL DEFAULT ((0)),
  40. [Remark] [nvarchar](500) NULL,
  41. [F1] [int] NOT NULL DEFAULT ((0)),
  42. [F2] [nvarchar](300) NULL,
  43. [LogTime] DateTime Default(Getdate()) Not Null,
  44. [InsOrUpd] char not null
  45. )
  46. GO

创建触发器:

  1. /*******   创建一个After DML触发器  ******/
  2. /********* 3w@live.cn 邀月***************/
  3. CREATE TRIGGER dbo.tri_LogDepartDemo
  4. ON [dbo].[DepartDemo]
  5. AFTER INSERT, Delete /************此处使用update与“Insert,Delete”效果是一样的,邀月注 **********/
  6. AS
  7. SET NOCOUNT ON --屏蔽触发器发送“受影响的行数”给应用程序

  8. -- Inserted rows
  9. INSERT [DepartChangeLogs]
  10. (DID,[DName], [DCode], [Manager], [ParentID],
  11. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
  12.  LogTime, InsOrUPD)
  13. SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],
  14. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
  15.  GETDATE(), 'I'
  16. FROM inserted i

  17. -- Deleted rows
  18. INSERT [DepartChangeLogs]
  19. (DID,[DName], [DCode], [Manager], [ParentID],
  20. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
  21.  LogTime, InsOrUPD)
  22. SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],
  23. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
  24.  GETDATE(), 'D'
  25. FROM deleted d
  26. GO

  27. INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
  28. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
  29. VALUES (N'国家统计局房产审计一科', N'0', N'胡不归', 0, N'DeomUser',
  30.  CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
  31.  1, N'专业评估全国房价,为老百姓谋福祉', 0, N'')
  32. GO

  33. ----该Update不会被触发器记录,但Update会生效
  34. UPDATE departDemo SET [Manager]='任我行' WHERE DID=101
  35. GO

  36. DELETE FROM departDemo where DID=101
  37. GO

  38. SELECT * FROM [DepartChangeLogs]

  统计效果:

邀月工作室
如果你觉得触发器过于浪费,你可以试着根据某些字段以缩小触发器的范围

  1. /********* 使用DML触发器记录特定列的修改 ***/
  2. /********* 3w@live.cn 邀月***************/
  3. CREATE TRIGGER dbo.[tri_LogDepartDemo2]
  4. ON [dbo].[DepartDemo]
  5. AFTER Update
  6. AS
  7. IF Update([Manager])
  8.     Begin
  9.         print '该部门主管实行终身任免制,不得中途更改!'
  10.         Rollback ----回滚Update操作
  11.     End

  12. GO
  13. UPDATE departDemo SET [Manager]='任我行' WHERE DID=101
  14. GO

  执行结果:


邀月工作室
  但触发器的缺陷也是显而易见的,使用触发器请注意以下几点:

1、触发器通常很隐蔽,换句话说,易忘记,特别在检查性能和逻辑问题时。

2、长时间运行的触发器会严重减慢数据操作,特别是在数据频繁修改的数据库中。

3、不记录日志的更新不会引起DML触发器的触发(如WRITETEXT、Trunacte table及批量插入操作)。

4、约束通常比触发器运行更快。

5、处理某些逻辑时,存储过程通常比触发器要更易维护和管理。

6、不允许在触发器中使用Select返回结果集。

  关于触发器的更多内容,请看MSDN()

 

二、使用Output子句

  官方解释:OutPut子句()返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息,或返回基于受这些语句影响的各行的表达式。 这些结果可以返回到处理应用程序,以供在确认消息、存档以及其他类似的应用程序要求中使用。 也可以将这些结果插入表或表变量。 另外,您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果,然后将这些结果插入目标表或视图。

 举例:

  1. /********* 使用Output记录表记录的修改 *****/
  2. /********* 3w@live.cn 邀月***************/

  3. ----删除前面的触发器
  4. Drop TRIGGER dbo.[tri_LogDepartDemo]
  5. DROP TRIGGER dbo.[tri_LogDepartDemo2]

  6. INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
  7. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
  8. OUTPUT Inserted.*,getdate(),'I' ---注意这行是新增的
  9. INTO DepartChangeLogs ---注意这行是新增的
  10. VALUES (N'发改委', N'0', N'向问天', 0, N'DeomUser',
  11.  CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
  12.  1, N'油价,我说了算', 0, N'')
  13. GO

  14. SELECT * FROM [DepartChangeLogs]

邀月工作室

  注意:

  1、从OUTPUT 中返回的列反映 INSERT、UPDATE 或 DELETE 语句完成之后但在触发器执行之前的数据。

  2、SQL Server 并不保证由使用 OUTPUT 子句的 DML 语句处理和返回行的顺序。

  3、与触发器相比,OutPut子句可以直接处理Merge语句。

   以上两种方法各有千秋,在合适的情况下采取合适的方法才是明智的选择,令人惊喜的是,SQL Server 2008起,为我们提供了更为强大的内建的方法-变更数据捕获(CDC,)和更改跟踪,下面我们隆重介绍它们。

三、使用“变更数据捕获”(CDC)功能

  SQL Server 2008提供了内建的方法变更数据捕获(Change Data Capture 即CDC)以实现异步跟踪用户表的数据修改,而且这一功能拥有最小的性能开销。可以用于其他数据源的持续更新,例如将OLTP数据库中的数据变更迁移到数据仓库数据库。

  要使用CDC功能,首先我们得在数据库中启用该功能。在此我们沿用上例中使用的数据库Testdb2

/**************异步跟踪数据更新演示*************/ 
/************* 3w@live.cn 邀月***************/ 
use master 
GO 
IF EXISTS (SELECT [name] FROM sys.databases WHERE name = 'TestDb2'
    drop DATABASE TestDb2 
Go 
CREATE DATABASE TestDb2 
GO 
--查看是否启用CDC 
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'TestDb2' 
USE TestDb2 
GO ----启用当前数据库的CDC功能 EXEC sys.sp_cdc_enable_db GO

  如果报15517错误,请换用其他owner,邀月注

SELECT is_cdc_enabled FROM sys.databases WHERE name = 'TestDb2' 
/* is_cdc_enabled 1 */ 
USE testDb2 
GO 
CREATE TABLE [dbo].[DepartDemo]
[DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY
[DName] [nvarchar](200) NULL
[DCode] [nvarchar](500) NULL
[Manager] [nvarchar](50) NULL
[ParentID] [int] NOT NULL DEFAULT ((0)), 
[AddUser] [nvarchar](50) NULL
[AddTime] [datetime] NULL
[ModUser] [nvarchar](50) NULL
[ModTime] [datetime] NULL
[CurState] [smallint] NOT NULL DEFAULT ((0)),
[Remark] [nvarchar](500) NULL,
[F1] [int] NOT NULL DEFAULT ((0)),
[F2] [nvarchar](300) NULL
)
GO
/**********************************
需要启用SQL Server Agent服务,否则会报错,邀月注 SQLServerAgent is not currently running so it cannot be notified of this action.
**********************************
*/
/****** 捕获所有的行变更,只返回行的净变更,其他默认 *******/
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'DepartDemo',
@role_name = NULL,
@capture_instance = NULL,
@supports_net_changes = 1,
@index_name = NULL,
@captured_column_list = NULL,
@filegroup_name = default

  注意此时,SQL Server 自启动了两个job,一个捕获,一个清除,注意清除是默认凌晨2点,清除72小时以上的数据。如果同一数据库的表中CDC已经启用,不会重建job。

/* 
Job 'cdc.TestDb2_capture' started successfully. 
Job 'cdc.TestDb2_cleanup' started successfully. 
*/ 
--确认表已经被跟踪 
SELECT is_tracked_by_cdc FROM sys.tables 
WHERE name = 'DepartDemo' and schema_id = SCHEMA_ID('dbo')
/* is_tracked_by_cdc 1 */
--确认
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'DepartDemo'

邀月工作室


可以看到,SQL Server 增加了一个表[cdc].[dbo_DepartDemo_CT] 
相比源表多了个字段: 
[__$start_lsn] 
,[__$end_lsn] 
,[__$seqval] 
,[__$operation] 
,[__$update_mask] 

邀月工作室

不建议直接查询该表,而应该使用下面的技巧

  1. USE TestDb2
  2. GO

  3. INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
  4. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
  5. VALUES (N'银监会', N'0', N'云中鹤', 0, N'DemoUser1',
  6. CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
  7. 1, N'监管汇率', 0, N'')

  8. INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
  9. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
  10. VALUES (N'统计局', N'0', N'神算子', 0, N'DemoUser2',
  11. CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
  12. 1, N'统计数据', 0, N'')
  13. GO

  14. UPDATE [dbo].[DepartDemo]
  15. SET Manager='段正淳'
  16. WHERE DID =101

  17. DELETE [dbo].[DepartDemo]
  18. WHERE DID = 102

  要查询变更,我们需要借助大名鼎鼎的日志序列号(Log Sequence Numbers)即LSN()来实现LSN级别的跟踪数据变更。 下面示例中sys.fn_cdc_map_time_to_lsn()用于LSN转换为时间。

  1. /******* 使用LSN 查看CDC记录 *********/

  2. --%28v=sql.100%29.aspx
  3. SELECT sys.fn_cdc_map_time_to_lsn
  4. ( 'smallest greater than or equal' , '2012-04-09 16:09:30') as BeginLSN

  5. /*
  6. BeginLSN
  7. 0x0000002C000000AA0003
  8. */

  9. SELECT sys.fn_cdc_map_time_to_lsn
  10. ( 'largest less than or equal' , '2012-04-09 23:59:59') as EndLSN

  11. /*
  12. EndLSN
  13. 0x0000002C000001C20005
  14. */

  15. /**************查看所有CDC记录*************/
  16. /************* 3w@live.cn 邀月***************/

  17. DECLARE @FromLSN varbinary(10) =
  18. sys.fn_cdc_map_time_to_lsn
  19. ( 'smallest greater than or equal' , '2012-04-09 16:09:30')

  20. DECLARE @ToLSN varbinary(10) =
  21. sys.fn_cdc_map_time_to_lsn
  22. ( 'largest less than or equal' , '2012-04-09 23:59:59')

  23. SELECT
  24. __$operation,
  25. __$update_mask,
  26. DID,
  27. DName,
  28. Manager
  29. FROM [cdc].[fn_cdc_get_all_changes_dbo_DepartDemo]
  30. (@FromLSN, @ToLSN, 'all')

  31. /************查看所有更新*************************

  32. __$operation __$update_mask DID DName Manager
  33. 2 0x1FFF 105 银监会 云中鹤
  34. 2 0x1FFF 106 统计局 神算子
  35. 1 0x1FFF 101 银监会 段正淳
  36. 1 0x1FFF 103 银监会 云中鹤
  37. 1 0x1FFF 104 统计局 神算子
  38. 1 0x1FFF 105 银监会 云中鹤
  39. 1 0x1FFF 106 统计局 神算子
  40. 2 0x1FFF 107 银监会 云中鹤
  41. 2 0x1FFF 108 统计局 神算子
  42. 4 0x0008 107 银监会 段正淳
  43. 1 0x1FFF 108 统计局 神算子
  44. */

  45. /**************查看所有CDC记录*************/
  46. /************* 3w@live.cn 邀月***************/
  47. DECLARE @FromLSN varbinary(10) =
  48. sys.fn_cdc_map_time_to_lsn
  49. ( 'smallest greater than or equal' , '2012-04-09 16:09:30')

  50. DECLARE @ToLSN varbinary(10) =
  51. sys.fn_cdc_map_time_to_lsn
  52. ( 'largest less than or equal' , '2012-04-09 23:59:59')

  53. --解释一下Operation的具体含义
  54. SELECT
  55. CASE __$operation
  56. WHEN 1 THEN 'DELETE'
  57. WHEN 2 THEN 'INSERT'
  58. WHEN 3 THEN 'Before UPDATE'
  59. WHEN 4 THEN 'After UPDATE'
  60. END Operation,
  61. __$update_mask,
  62. DID,
  63. DName,
  64. Manager
  65. FROM [cdc].[fn_cdc_get_all_changes_dbo_DepartDemo]
  66. (@FromLSN, @ToLSN, 'all update old')

邀月工作室

  1. /**************查看净更改(Net changes)CDC记录*************/
  2. /************* 3w@live.cn 邀月 ***************/

  3. INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
  4. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
  5. VALUES (N'药监局', N'0', N'蝶谷医仙', 0, N'DemoUser3',
  6. CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),
  7. 1, N'制定药价', 0, N'')
  8. GO

  9. UPDATE [dbo].[DepartDemo]
  10. SET Manager='胡青牛'
  11. WHERE DID =109

  12. DECLARE @FromLSN varbinary(10) =
  13. sys.fn_cdc_map_time_to_lsn
  14. ( 'smallest greater than or equal' , '2012-04-09 16:09:30')

  15. DECLARE @ToLSN varbinary(10) =
  16. sys.fn_cdc_map_time_to_lsn
  17. ( 'largest less than or equal' , '2012-04-09 23:59:59')

  18. SELECT
  19. CASE __$operation
  20. WHEN 1 THEN 'DELETE'
  21. WHEN 2 THEN 'INSERT'
  22. WHEN 3 THEN 'Before UPDATE'
  23. WHEN 4 THEN 'After UPDATE'
  24. WHEN 5 THEN 'MERGE'
  25. END Operation,
  26. __$update_mask,
  27. DID,
  28. DName,
  29. Manager
  30. FROM [cdc].[fn_cdc_get_net_changes_dbo_DepartDemo]
  31. (@FromLSN, @ToLSN, 'all with mask')

邀月工作室

  我们还可以通过转换CDC更新掩码获得更为直观的结果,这里需要借助于另外两个函数sys.fn_cdc_is_bit_set()和sys.fn_cdc_get_column_ordinal()

  1. /************** 转换CDC更新掩码 *************/
  2. /************* 3w@live.cn 邀月 **************/

  3. UPDATE dbo.[DepartDemo]
  4. SET [Manager] = '东方不败'
  5. WHERE DID =107

  6. UPDATE dbo.[DepartDemo]
  7. SET ParentID = 109
  8. WHERE DID =107

  9. DECLARE @FromLSN varbinary(10) =
  10. sys.fn_cdc_map_time_to_lsn
  11. ( 'smallest greater than or equal' , '2012-04-09 16:09:30')

  12. DECLARE @ToLSN varbinary(10) =
  13. sys.fn_cdc_map_time_to_lsn
  14. ( 'largest less than or equal' , '2012-04-09 23:59:59')

  15. SELECT
  16. sys.fn_cdc_is_bit_set (
  17. sys.fn_cdc_get_column_ordinal (
  18. 'dbo_DepartDemo' , 'Manager' ),
  19. __$update_mask) Manager_Updated,
  20. sys.fn_cdc_is_bit_set (
  21. sys.fn_cdc_get_column_ordinal (
  22. 'dbo_DepartDemo' , 'ParentID' ),
  23. __$update_mask) ParentID_Updated,
  24. DID,
  25. Manager,
  26. ParentID
  27. FROM cdc.fn_cdc_get_all_changes_dbo_DepartDemo
  28. (@FromLSN, @ToLSN, 'all')
  29. WHERE __$operation = 4

邀月工作室

  除了前面介绍的指定LSN边界的方法,SQL Server还提供了一系列的获取边界的方法:

sys.fn_cdc_get_max_lsn()

sys.fn_cdc_get_min_lsn()

sys.fn_cdc_increment_lsn()

sys.fn_cdc_decrement_lsn()

  示例如下:

/************** 获取LSN边界的其他方法 *************/ 
/************* 3w@live.cn 邀月 **************/
--获取最小边界
SELECT sys.fn_cdc_get_min_lsn ('dbo_DepartDemo') Min_LSN
--获取可用的最大边界
SELECT sys.fn_cdc_get_max_lsn () Max_LSN
--获取最大边界的下一个序号
SELECT sys.fn_cdc_increment_lsn (sys.fn_cdc_get_max_lsn())
New_Lower_Bound_LSN
--获取最大边界的前一个序号
SELECT sys.fn_cdc_decrement_lsn (sys.fn_cdc_get_max_lsn())
New_Lower_Bound_Minus_one_LSN

 邀月工作室

  通过以下存储过程在数据库和表级禁用CDC

sys.sp_cdc_disable_table ()

sys.sp_cdc_disable_db()注意,该命令同时也删除了CDC架构和相关的SQL代理作业。

/************** 在数据库和表级禁用CDC *************/
/************* 3w@live.cn 邀月 **************/
EXEC sys.sp_cdc_disable_table 'dbo', 'DepartDemo', 'all'
SELECT is_tracked_by_cdc FROM sys.tables
WHERE name = 'DepartDemo' and schema_id = SCHEMA_ID('dbo')
--当前数据库上禁用CDC
EXEC sys.sp_cdc_disable_db

 

四、使用“更改跟踪”以最小的磁盘开销跟踪净数据更改

  CDC可以用来对数据库和数据仓库的持续数据变更进行异步数据跟踪,而SQL Server 2008中新增的“更改跟踪”却是一个同步进程,是DML操作本身(I/D/U)事务的一部分,它的最大优势是以最小的磁盘开销来侦测净行变更,它允许修改的数据以事务一致的形式表现,并提供了检测数据冲突的能力。它甚至可以根据外部传入的应用程序上下文,来完成更细颗粒度的更改处理,参看WITH CHANGE_TRACKING_CONTEXT ()

/***使用“更改跟踪”以最小的磁盘开销跟踪净数据更改****/
/************* 3w@live.cn 邀月 **************/
IF EXISTS (SELECT [name] FROM sys.databases WHERE name = 'TestDb4')
  drop DATABASE TestDb4
Go CREATE DATABASE TestDb4
GO
--启用更新跟踪,36小时清理一次
ALTER DATABASE TestDb4 SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 36 HOURS, AUTO_CLEANUP = ON)

  注意,下一步是允许快照隔离,这是微软推祟的“最佳实践”,尽管这样行版本的生成会增加额外的空间使用,从而会增加总的I/O数量,但不使用快照会引发事务不一致的变更信息。

  1. ALTER DATABASE TestDb4
  2. SET ALLOW_SNAPSHOT_ISOLATION ON
  3. GO

  4. SELECT DB_NAME(database_id) 数据库名称,is_auto_cleanup_on,
  5. retention_period,retention_period_units_desc
  6. FROM sys.change_tracking_databases
  7. /*
  8. 数据库名称 is_auto_cleanup_on retention_period retention_period_units_desc
  9. TestDb4 1 36 HOURS
  10. */

  11. USE TestDb4
  12. GO
  13. --创建测试表
  14. CREATE TABLE dbo.DepartDemo
  15. ([DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY,
  16. [DName] [nvarchar](200) NULL,
  17. [Manager] [nvarchar](50) NULL,
  18. [ParentID] [int] NOT NULL DEFAULT ((0)),
  19. [CurState] [smallint] NOT NULL DEFAULT ((0)),
  20. )
  21. GO

  22. ----TRUNCATE table dbo.DepartDemo
  23. ----GO

  24. --启用表的列更新跟踪
  25. ALTER TABLE dbo.DepartDemo
  26. ENABLE CHANGE_TRACKING
  27. WITH (TRACK_COLUMNS_UPDATED = ON)

  28. --确认是否更新跟踪开启
  29. SELECT OBJECT_NAME(object_id) ObjNM,is_track_columns_updated_on
  30. FROM sys.change_tracking_tables

  31. /*
  32. ObjNM is_track_columns_updated_on
  33. DepartDemo 1
  34. */

  35. --增加测试数据
  36. INSERT dbo.DepartDemo
  37. (DName,ParentID)
  38. VALUES
  39. ('明教', 0),
  40. ('五行集', 101),
  41. ('少林派',0)

  42. SELECT * FROM dbo.DepartDemo

  43. --当前版本
  44. SELECT CHANGE_TRACKING_CURRENT_VERSION ()
  45. as 当前版本
  46. /*
  47. 当前版本
  48. 1
  49. */
  50. SELECT CHANGE_TRACKING_MIN_VALID_VERSION
  51. ( OBJECT_ID('dbo.DepartDemo') )as 最小可用版本

  52. /*
  53. 最小可用版本
  54. 0
  55. */

函数ChangeTable有两种用法来检测更改:
一、使用Changes关键字
二、使用Version关键字

/* 一、使用Changes关键字 */
SELECT DID,SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION
FROM CHANGETABLE (CHANGES dbo.DepartDemo, 0) AS CT

邀月工作室

UPDATE dbo.DepartDemo SET Manager='张无忌' WHERE DID = 101
UPDATE dbo.DepartDemo SET [DName] = '五行旗' WHERE DID = 102
DELETE dbo.DepartDemo WHERE DID = 103
SELECT CHANGE_TRACKING_CURRENT_VERSION () as 当前版本 /* 当前版本 4 */
--版本1之后的更改
SELECT DID, SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS FROM CHANGETABLE (CHANGES dbo.DepartDemo, 1) AS CT

邀月工作室

--返回哪些列被修改,1为真,0为假
SELECT DID,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.DepartDemo'),'DName', 'ColumnId') ,
SYS_CHANGE_COLUMNS) 是否改变DName,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.DepartDemo'), 'Manager', 'ColumnId') ,
SYS_CHANGE_COLUMNS) 是否改变Manager
FROM CHANGETABLE (CHANGES dbo.DepartDemo, 1) AS CT
WHERE SYS_CHANGE_OPERATION = 'U'
/* DID 是否改变DName 是否改变Manager 101 0 1 102 1 0 */

 

/* 二、使用Version关键字 */
SELECT d.DID, d.DName, d.Manager, ct.SYS_CHANGE_VERSION
FROM dbo.DepartDemo d
CROSS APPLY CHANGETABLE (VERSION dbo.DepartDemo , (DID), (d.DID)) as ct


邀月工作室

UPDATE dbo.DepartDemo SET DName = '中原明教', CurState = 0
WHERE DID = 101
SELECT d.DID, d.DName, d.Manager, ct.SYS_CHANGE_VERSION
FROM dbo.DepartDemo d
CROSS APPLY CHANGETABLE (VERSION dbo.DepartDemo , (DID), (d.DID)) as ct

邀月工作室

SELECT CHANGE_TRACKING_CURRENT_VERSION () as 当前版本 /* 当前版本 5 */
--跟踪外部程序哪一部分引起的更改,这样好找出源头
DECLARE @context varbinary(128) = CAST('明教内讧引起分裂' as varbinary(128));
WITH CHANGE_TRACKING_CONTEXT (@context)
INSERT dbo.DepartDemo (DName, Manager) VALUES ('天鹰教', '殷天正')
--查询Context更改
SELECT DID, SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION, CAST(SYS_CHANGE_CONTEXT as varchar) ApplicationContext
FROM CHANGETABLE (CHANGES dbo.DepartDemo, 5) AS CT
/* DID SYS_CHANGE_OPERATION SYS_CHANGE_VERSION ApplicationContext 104 I 6 明教内讧引起分裂 */

 

 邀月工作室

 小结:

  本文总结了SQL Server中记录数据变更的四个方法:触发器、Output子句、SQL Server 2008中新增的变更数据捕获(CDC)功能、同步更改跟踪。其中后两个是SQL Server 2008中新增的功能,在SQL Server 2012中更是与Always ON紧密集成。

  1、不建议前两个。

  2、CDC用以实现异步跟踪用户表的数据修改,而且这一功能拥有最小的性能开销,可以用于其他数据源的持续更新,例如将OLTP数据库中的数据变更迁移到数据仓库数据库。

  3、”更改跟踪”的最大优势是以最小的磁盘开销来侦测净行变更,它允许修改的数据以事务一致的形式表现,并提供了检测数据冲突的能力。

 

其他推荐文章:

1、在VS中如何将数据同步配置为使用 SQL Server 更改跟踪()

2、SQL Server 2012中复制、更改跟踪、更改数据捕获和 AlwaysOn 可用性组 (SQL Server)()

 

原谅地址: http://www.cnblogs.com/downmoon/archive/2012/04/09/2439462.html

阅读(3923) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~