SQL Server 2008新增的变更数据捕获和更改跟踪

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

一、触发器

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

准备基础数据：

USE testDb2  
GO  
--创建两个测试表  
IF NOT OBJECT_ID('DepartDemo') IS NULL 
DROP TABLE [DepartDemo]  
GO  
IF NOT OBJECT_ID('DepartChangeLogs') IS NULL 
DROP TABLE [DepartChangeLogs]  
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  
--记录日志表  
CREATE TABLE [DepartChangeLogs]  
([LogID] [bigint] IDENTITY(1001,1) NOT NULL PRIMARY KEY,  
[DID] [int] NOT NULL,  
[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,  
[LogTime] DateTime Default(Getdate()) Not Null,  
[InsOrUpd] char not null 
)  
GO 

创建触发器：

/******* 创建一个After DML触发器 ******/  
/********* 3w@live.cn 邀月***************/  
CREATE TRIGGER dbo.tri_LogDepartDemo  
ON [dbo].[DepartDemo]  
AFTER INSERT, Delete /************此处使用update与“Insert,Delete”效果是一样的，邀月注 **********/  
AS 
SET NOCOUNT ON --屏蔽触发器发送“受影响的行数”给应用程序  
-- Inserted rows  
INSERT [DepartChangeLogs]  
(DID,[DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],  
LogTime, InsOrUPD)  
SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],  
GETDATE(), 'I' 
FROM inserted i  
-- Deleted rows  
INSERT [DepartChangeLogs]  
(DID,[DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],  
LogTime, InsOrUPD)  
SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],  
GETDATE(), 'D' 
FROM deleted d  
GO  
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])  
VALUES (N'国家统计局房产审计一科', N'0', N'胡不归', 0, N'DeomUser',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'专业评估全国房价，为老百姓谋福祉', 0, N'')  
GO  
----该Update不会被触发器记录，但Update会生效  
UPDATE departDemo SET [Manager]='任我行' WHERE DID=101  
GO  
DELETE FROM departDemo where DID=101  
GO  
SELECT * FROM [DepartChangeLogs] 

统计效果：

如果你觉得触发器过于浪费，你可以试着根据某些字段以缩小触发器的范围

/********* 使用DML触发器记录特定列的修改 ***/  
/********* 3w@live.cn 邀月***************/  
CREATE TRIGGER dbo.[tri_LogDepartDemo2]  
ON [dbo].[DepartDemo]  
AFTER Update 
AS 
IF Update([Manager])  
Begin 
print '该部门主管实行终身任免制，不得中途更改！' 
Rollback ----回滚Update操作  
End 
GO  
UPDATE departDemo SET [Manager]='任我行' WHERE DID=101  
GO 

执行结果：

但触发器的缺陷也是显而易见的，使用触发器请注意以下几点：

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

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

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

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

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

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

关于触发器的更多内容，请看MSDN（http://msdn.microsoft.com/zh-cn/library/ms189799.aspx）

二、使用Output子句

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

举例：

/********* 使用Output记录表记录的修改 *****/  
/********* 3w@live.cn 邀月***************/  
----删除前面的触发器  
Drop TRIGGER dbo.[tri_LogDepartDemo]  
DROP TRIGGER dbo.[tri_LogDepartDemo2]  
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])  
OUTPUT Inserted.*,getdate(),'I' ---注意这行是新增的  
INTO DepartChangeLogs ---注意这行是新增的  
VALUES (N'发改委', N'0', N'向问天', 0, N'DeomUser',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'油价，我说了算', 0, N'')  
GO  
SELECT * FROM [DepartChangeLogs] 

注意：

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

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

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

以上两种方法各有千秋，在合适的情况下采取合适的方法才是明智的选择，令人惊喜的是，SQL Server 2008起，为我们提供了更为强大的内建的方法－变更数据捕获（CDC，http://msdn.microsoft.com/zh-cn/library/bb500244%28v=sql.100%29.aspx）和更改跟踪，下面我们隆重介绍它们。

三、使用“变更数据捕获”（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,一个捕获，一个清除，注意清除是默认凌晨２点，清除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' 

增加了一个表[cdc].[dbo_DepartDemo_CT]

相比源表多了个字段：

[__$start_lsn]  
,[__$end_lsn]  
,[__$seqval]  
,[__$operation]  
,[__$update_mask]  

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

USE TestDb2  
GO  
 
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])  
VALUES (N'银监会', N'0', N'云中鹤', 0, N'DemoUser1',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'监管汇率', 0, N'')  
 
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])  
VALUES (N'统计局', N'0', N'神算子', 0, N'DemoUser2',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'统计数据', 0, N'')  
GO  
 
UPDATE [dbo].[DepartDemo]  
SET Manager='段正淳' 
WHERE DID =101  
 
DELETE [dbo].[DepartDemo]  
WHERE DID = 102 

要查询变更，我们需要借助大名鼎鼎的日志序列号（Log Sequence Numbers）即LSN（http://msdn.microsoft.com/zh-cn/library/ms190411%28v=sql.100%29.aspx）来实现LSN级别的跟踪数据变更。 下面示例中sys.fn_cdc_map_time_to_lsn（http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx）用于LSN转换为时间。

/******* 使用LSN 查看CDC记录 *********/  
 
--http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx  
SELECT sys.fn_cdc_map_time_to_lsn  
( 'smallest greater than or equal' , '2012-04-09 16:09:30') as BeginLSN  
 
/*  
BeginLSN  
0x0000002C000000AA0003  
*/  
 
SELECT sys.fn_cdc_map_time_to_lsn  
( 'largest less than or equal' , '2012-04-09 23:59:59') as EndLSN  
 
/*  
EndLSN  
0x0000002C000001C20005  
*/  
 
/**************查看所有CDC记录*************/  
/************* 3w@live.cn 邀月***************/  
 
DECLARE @FromLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'smallest greater than or equal' , '2012-04-09 16:09:30')  
 
DECLARE @ToLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'largest less than or equal' , '2012-04-09 23:59:59')  
 
SELECT 
__$operation,  
__$update_mask,  
DID,  
DName,  
Manager  
FROM [cdc].[fn_cdc_get_all_changes_dbo_DepartDemo]  
(@FromLSN, @ToLSN, 'all')  
 
/************查看所有更新*************************  
 
__$operation __$update_mask DID DName Manager  
2 0x1FFF 105 银监会 云中鹤  
2 0x1FFF 106 统计局 神算子  
1 0x1FFF 101 银监会 段正淳  
1 0x1FFF 103 银监会 云中鹤  
1 0x1FFF 104 统计局 神算子  
1 0x1FFF 105 银监会 云中鹤  
1 0x1FFF 106 统计局 神算子  
2 0x1FFF 107 银监会 云中鹤  
2 0x1FFF 108 统计局 神算子  
4 0x0008 107 银监会 段正淳  
1 0x1FFF 108 统计局 神算子  
*/  
 
/**************查看所有CDC记录*************/  
/************* 3w@live.cn 邀月***************/  
DECLARE @FromLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'smallest greater than or equal' , '2012-04-09 16:09:30')  
 
DECLARE @ToLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'largest less than or equal' , '2012-04-09 23:59:59')  
 
--解释一下Operation的具体含义  
SELECT 
CASE __$operation  
WHEN 1 THEN 'DELETE' 
WHEN 2 THEN 'INSERT' 
WHEN 3 THEN 'Before UPDATE' 
WHEN 4 THEN 'After UPDATE' 
END Operation,  
__$update_mask,  
DID,  
DName,  
Manager  
FROM [cdc].[fn_cdc_get_all_changes_dbo_DepartDemo]  
(@FromLSN, @ToLSN, 'all update old') 

/**************查看净更改（Net changes）CDC记录*************/  
/************* 3w@live.cn 邀月 ***************/  
 
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])  
VALUES (N'药监局', N'0', N'蝶谷医仙', 0, N'DemoUser3',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'制定药价', 0, N'')  
GO  
 
UPDATE [dbo].[DepartDemo]  
SET Manager='胡青牛' 
WHERE DID =109  
 
DECLARE @FromLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'smallest greater than or equal' , '2012-04-09 16:09:30')  
 
DECLARE @ToLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'largest less than or equal' , '2012-04-09 23:59:59')  
 
SELECT 
CASE __$operation  
WHEN 1 THEN 'DELETE' 
WHEN 2 THEN 'INSERT' 
WHEN 3 THEN 'Before UPDATE' 
WHEN 4 THEN 'After UPDATE' 
WHEN 5 THEN 'MERGE' 
END Operation,  
__$update_mask,  
DID,  
DName,  
Manager  
FROM [cdc].[fn_cdc_get_net_changes_dbo_DepartDemo]  
(@FromLSN, @ToLSN, 'all with mask') 

我们还可以通过转换CDC更新掩码获得更为直观的结果，这里需要借助于另外两个函数sys.fn_cdc_is_bit_set（http://msdn.microsoft.com/zh-cn/library/bb500241%28v=SQL.110%29.aspx）和sys.fn_cdc_get_column_ordinal（http://msdn.microsoft.com/zh-cn/library/bb522549%28v=SQL.100%29.aspx）

/************** 转换CDC更新掩码 *************/  
/************* 3w@live.cn 邀月 **************/  
 
UPDATE dbo.[DepartDemo]  
SET [Manager] = '东方不败' 
WHERE DID =107  
 
UPDATE dbo.[DepartDemo]  
SET ParentID = 109  
WHERE DID =107  
 
DECLARE @FromLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'smallest greater than or equal' , '2012-04-09 16:09:30')  
 
DECLARE @ToLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'largest less than or equal' , '2012-04-09 23:59:59')  
 
SELECT 
sys.fn_cdc_is_bit_set (  
sys.fn_cdc_get_column_ordinal (  
'dbo_DepartDemo' , 'Manager' ),  
__$update_mask) Manager_Updated,  
sys.fn_cdc_is_bit_set (  
sys.fn_cdc_get_column_ordinal (  
'dbo_DepartDemo' , 'ParentID' ),  
__$update_mask) ParentID_Updated,  
DID,  
Manager,  
ParentID  
FROM cdc.fn_cdc_get_all_changes_dbo_DepartDemo  
(@FromLSN, @ToLSN, 'all')  
WHERE __$operation = 4 

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

sys.fn_cdc_get_max_lsn（http://msdn.microsoft.com/zh-cn/library/bb500304%28v=sql.100%29.aspx）

sys.fn_cdc_get_min_lsn（http://msdn.microsoft.com/zh-cn/library/bb510621%28v=sql.100%29.aspx）

sys.fn_cdc_increment_lsn（http://msdn.microsoft.com/zh-cn/library/bb510745%28v=sql.100%29.aspx）

sys.fn_cdc_decrement_lsn（http://msdn.microsoft.com/zh-cn/library/bb500246%28v=sql.100%29.aspx）

示例如下：

/************** 获取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 （http://msdn.microsoft.com/zh-cn/library/bb510702(v=sql.100).aspx）

sys.sp_cdc_disable_db（http://msdn.microsoft.com/zh-cn/library/bb522508(v=sql.100).aspx）注意，该命令同时也删除了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 （http://msdn.microsoft.com/zh-cn/library/bb895330%28v=sql.100%29.aspx）

/***使用“更改跟踪”以最小的磁盘开销跟踪净数据更改****/  
/************* 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数量,但不使用快照会引发事务不一致的变更信息。

ALTER DATABASE TestDb4  
SET ALLOW_SNAPSHOT_ISOLATION ON 
GO  
 
SELECT DB_NAME(database_id) 数据库名称,is_auto_cleanup_on,  
retention_period,retention_period_units_desc  
FROM sys.change_tracking_databases  
/*  
数据库名称 is_auto_cleanup_on retention_period retention_period_units_desc  
TestDb4 1 36 HOURS  
*/  
 
USE TestDb4  
GO  
--创建测试表  
CREATE TABLE dbo.DepartDemo  
([DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY,  
[DName] [nvarchar](200) NULL,  
[Manager] [nvarchar](50) NULL,  
[ParentID] [int] NOT NULL DEFAULT ((0)),  
[CurState] [smallint] NOT NULL DEFAULT ((0)),  
)  
GO  
 
----TRUNCATE table dbo.DepartDemo  
----GO  
 
--启用表的列更新跟踪  
ALTER TABLE dbo.DepartDemo  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON)  
 
--确认是否更新跟踪开启  
SELECT OBJECT_NAME(object_id) ObjNM,is_track_columns_updated_on  
FROM sys.change_tracking_tables  
 
/*  
ObjNM is_track_columns_updated_on  
DepartDemo 1  
*/  
 
--增加测试数据  
INSERT dbo.DepartDemo  
(DName,ParentID)  
VALUES 
('明教', 0),  
('五行集', 101),  
('少林派',0)  
 
SELECT * FROM dbo.DepartDemo  
 
--当前版本  
SELECT CHANGE_TRACKING_CURRENT_VERSION ()  
as 当前版本  
/*  
当前版本  
1  
*/  
SELECT CHANGE_TRACKING_MIN_VALID_VERSION  
( OBJECT_ID('dbo.DepartDemo') )as 最小可用版本  
 
/*  
最小可用版本  
0  
*/ 

函数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 更改跟踪（http://msdn.microsoft.com/zh-cn/library/cc714038.aspx）

2、SQL Server 2012中复制、更改跟踪、更改数据捕获和 AlwaysOn 可用性组 (SQL Server)（http://msdn.microsoft.com/zh-cn/library/hh403414%28v=sql.110%29.aspx）

原文链接：http://blog.csdn.net/downmoon/article/details/7443627

【编辑推荐】

1. SQL Server 2008中增强的汇总技巧
2. 从SQL Server删除大数据说开去
3. 监控SQL Server的运行状况
4. SQL Server数据库恢复案例分享
5. SQL Server镜像功能完全实现