SQL Server 2008新增的變更數(shù)據(jù)捕獲和更改跟蹤

作者：邀月 2012-04-13 13:26:30

本文主要介紹SQL Server中記錄數(shù)據(jù)變更的四個(gè)方法：觸發(fā)器、Output子句、SQL Server 2008中新增的變更數(shù)據(jù)捕獲（Change Data Capture 即CDC）功能、同步更改跟蹤。其中后兩個(gè)為SQL Server 2008所新增。

一、觸發(fā)器

在SQL Server的早期版本中，如果要記錄某個(gè)表或視圖的Insert/Update/Delete操作，我們可以借助觸發(fā)器（Trigger）（http://msdn.microsoft.com/zh-cn/library/ms189799.aspx）, 這在數(shù)據(jù)量較小的情況下往往是有效的方式之一，其中后觸發(fā)器（After Trigger）只能跟蹤表的三個(gè)操作中的任意組合，而前觸發(fā)器（Instead Of trigger）可以處理表和視圖的更新（即使普通的Update View語句在某些列不明確的情況下報(bào)錯(cuò)）。我們看兩個(gè)例子：

準(zhǔn)備基礎(chǔ)數(shù)據(jù)：

USE testDb2  
GO  
--創(chuàng)建兩個(gè)測(cè)試表  
IF NOT OBJECT_ID('DepartDemo') IS NULL 
DROP TABLE [DepartDemo]  
GO  
IF NOT OBJECT_ID('DepartChangeLogs') IS NULL 
DROP TABLE [DepartChangeLogs]  
GO  
--測(cè)試表  
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

創(chuàng)建觸發(fā)器：

/******* 創(chuàng)建一個(gè)After DML觸發(fā)器 ******/  
/********* 3w@live.cn 邀月***************/  
CREATE TRIGGER dbo.tri_LogDepartDemo  
ON [dbo].[DepartDemo]  
AFTER INSERT, Delete /************此處使用update與“Insert,Delete”效果是一樣的，邀月注 **********/  
AS 
SET NOCOUNT ON --屏蔽觸發(fā)器發(fā)送“受影響的行數(shù)”給應(yīng)用程序  
-- 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'國(guó)家統(tǒng)計(jì)局房產(chǎn)審計(jì)一科', N'0', N'胡不歸', 0, N'DeomUser',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'專業(yè)評(píng)估全國(guó)房?jī)r(jià)，為老百姓謀福祉', 0, N'')  
GO  
----該Update不會(huì)被觸發(fā)器記錄，但Update會(huì)生效  
UPDATE departDemo SET [Manager]='任我行' WHERE DID=101  
GO  
DELETE FROM departDemo where DID=101  
GO  
SELECT * FROM [DepartChangeLogs]

統(tǒng)計(jì)效果：

邀月工作室

如果你覺得觸發(fā)器過于浪費(fèi)，你可以試著根據(jù)某些字段以縮小觸發(fā)器的范圍

/********* 使用DML觸發(fā)器記錄特定列的修改 ***/  
/********* 3w@live.cn 邀月***************/  
CREATE TRIGGER dbo.[tri_LogDepartDemo2]  
ON [dbo].[DepartDemo]  
AFTER Update 
AS 
IF Update([Manager])  
Begin 
print '該部門主管實(shí)行終身任免制，不得中途更改！' 
Rollback ----回滾Update操作  
End 
GO  
UPDATE departDemo SET [Manager]='任我行' WHERE DID=101  
GO

執(zhí)行結(jié)果：

邀月工作室

但觸發(fā)器的缺陷也是顯而易見的，使用觸發(fā)器請(qǐng)注意以下幾點(diǎn)：

1、觸發(fā)器通常很隱蔽，換句話說，易忘記，特別在檢查性能和邏輯問題時(shí)。

2、長(zhǎng)時(shí)間運(yùn)行的觸發(fā)器會(huì)嚴(yán)重減慢數(shù)據(jù)操作，特別是在數(shù)據(jù)頻繁修改的數(shù)據(jù)庫(kù)中。

3、不記錄日志的更新不會(huì)引起DML觸發(fā)器的觸發(fā)（如WRITETEXT、Trunacte table及批量插入操作）。

4、約束通常比觸發(fā)器運(yùn)行更快。

5、處理某些邏輯時(shí)，存儲(chǔ)過程通常比觸發(fā)器要更易維護(hù)和管理。

6、不允許在觸發(fā)器中使用Select返回結(jié)果集。

關(guān)于觸發(fā)器的更多內(nèi)容，請(qǐng)看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 語句影響的各行中的信息，或返回基于受這些語句影響的各行的表達(dá)式。這些結(jié)果可以返回到處理應(yīng)用程序，以供在確認(rèn)消息、存檔以及其他類似的應(yīng)用程序要求中使用。也可以將這些結(jié)果插入表或表變量。另外，您可以捕獲嵌入的 INSERT、UPDATE、DELETE 或 MERGE 語句中 OUTPUT 子句的結(jié)果，然后將這些結(jié)果插入目標(biāo)表或視圖。

舉例：

/********* 使用Output記錄表記錄的修改 *****/  
/********* 3w@live.cn 邀月***************/  
----刪除前面的觸發(fā)器  
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'發(fā)改委', N'0', N'向問天', 0, N'DeomUser',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'油價(jià)，我說了算', 0, N'')  
GO  
SELECT * FROM [DepartChangeLogs]

邀月工作室

注意：

1、從OUTPUT 中返回的列反映 INSERT、UPDATE 或 DELETE 語句完成之后但在觸發(fā)器執(zhí)行之前的數(shù)據(jù)。

2、SQL Server 并不保證由使用 OUTPUT 子句的 DML 語句處理和返回行的順序。

3、與觸發(fā)器相比，OutPut子句可以直接處理Merge語句。

以上兩種方法各有千秋，在合適的情況下采取合適的方法才是明智的選擇，令人驚喜的是，SQL Server 2008起，為我們提供了更為強(qiáng)大的內(nèi)建的方法－變更數(shù)據(jù)捕獲（CDC，http://msdn.microsoft.com/zh-cn/library/bb500244%28v=sql.100%29.aspx）和更改跟蹤，下面我們隆重介紹它們。

三、使用“變更數(shù)據(jù)捕獲”（CDC）功能

SQL Server 2008提供了內(nèi)建的方法“”變更數(shù)據(jù)捕獲“”（Change Data Capture 即CDC）以實(shí)現(xiàn)異步跟蹤用戶表的數(shù)據(jù)修改，而且這一功能擁有最小的性能開銷。可以用于其他數(shù)據(jù)源的持續(xù)更新，例如將OLTP數(shù)據(jù)庫(kù)中的數(shù)據(jù)變更遷移到數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)庫(kù)。

要使用CDC功能，首先我們得在數(shù)據(jù)庫(kù)中啟用該功能。在此我們沿用上例中使用的數(shù)據(jù)庫(kù)Testdb2

/**************異步跟蹤數(shù)據(jù)更新演示*************/  
/************* 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  
----啟用當(dāng)前數(shù)據(jù)庫(kù)的CDC功能  
EXEC sys.sp_cdc_enable_db  
GO  
 
/**************  
如果報(bào)15517錯(cuò)誤，請(qǐng)換用其他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服務(wù)，否則會(huì)報(bào)錯(cuò)，邀月注  
SQLServerAgent is not currently running so it cannot be notified of this action.  
***********************************/  
 
/****** 捕獲所有的行變更，只返回行的凈變更，其他默認(rèn) *******/  
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

注意此時(shí)，SQL Server 自啟動(dòng)了兩個(gè)job,一個(gè)捕獲，一個(gè)清除，注意清除是默認(rèn)凌晨２點(diǎn)，清除72小時(shí)以上的數(shù)據(jù)。如果同一數(shù)據(jù)庫(kù)的表中CDC已經(jīng)啟用，不會(huì)重建job。

/*  
Job 'cdc.TestDb2_capture' started successfully.  
Job 'cdc.TestDb2_cleanup' started successfully.  
*/  
 
--確認(rèn)表已經(jīng)被跟蹤  
 
SELECT is_tracked_by_cdc FROM sys.tables  
WHERE name = 'DepartDemo' and schema_id = SCHEMA_ID('dbo')  
/*  
is_tracked_by_cdc  
1  
*/  
 
--確認(rèn)  
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'DepartDemo'

邀月工作室

增加了一個(gè)表[cdc].[dbo_DepartDemo_CT]

相比源表多了個(gè)字段：

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

邀月工作室

不建議直接查詢?cè)摫恚鴳?yīng)該使用下面的技巧：

USE TestDb2  
GO  
 
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])  
VALUES (N'銀監(jiān)會(huì)', N'0', N'云中鶴', 0, N'DemoUser1',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'監(jiān)管匯率', 0, N'')  
 
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],  
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])  
VALUES (N'統(tǒng)計(jì)局', N'0', N'神算子', 0, N'DemoUser2',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'統(tǒng)計(jì)數(shù)據(jù)', 0, N'')  
GO  
 
UPDATE [dbo].[DepartDemo]  
SET Manager='段正淳' 
WHERE DID =101  
 
DELETE [dbo].[DepartDemo]  
WHERE DID = 102

要查詢變更，我們需要借助大名鼎鼎的日志序列號(hào)（Log Sequence Numbers）即LSN（http://msdn.microsoft.com/zh-cn/library/ms190411%28v=sql.100%29.aspx）來實(shí)現(xiàn)LSN級(jí)別的跟蹤數(shù)據(jù)變更。下面示例中sys.fn_cdc_map_time_to_lsn（http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx）用于LSN轉(zhuǎn)換為時(shí)間。

/******* 使用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 銀監(jiān)會(huì) 云中鶴  
2 0x1FFF 106 統(tǒng)計(jì)局 神算子  
1 0x1FFF 101 銀監(jiān)會(huì) 段正淳  
1 0x1FFF 103 銀監(jiān)會(huì) 云中鶴  
1 0x1FFF 104 統(tǒng)計(jì)局 神算子  
1 0x1FFF 105 銀監(jiān)會(huì) 云中鶴  
1 0x1FFF 106 統(tǒng)計(jì)局 神算子  
2 0x1FFF 107 銀監(jiān)會(huì) 云中鶴  
2 0x1FFF 108 統(tǒng)計(jì)局 神算子  
4 0x0008 107 銀監(jiān)會(huì) 段正淳  
1 0x1FFF 108 統(tǒng)計(jì)局 神算子  
*/  
 
/**************查看所有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'藥監(jiān)局', N'0', N'蝶谷醫(yī)仙', 0, N'DemoUser3',  
CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime),  
1, N'制定藥價(jià)', 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')

邀月工作室

我們還可以通過轉(zhuǎn)換CDC更新掩碼獲得更為直觀的結(jié)果，這里需要借助于另外兩個(gè)函數(shù)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）

/************** 轉(zhuǎn)換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  
--獲取最大邊界的下一個(gè)序號(hào)  
SELECT sys.fn_cdc_increment_lsn (sys.fn_cdc_get_max_lsn()) New_Lower_Bound_LSN  
--獲取最大邊界的前一個(gè)序號(hào)  
SELECT sys.fn_cdc_decrement_lsn (sys.fn_cdc_get_max_lsn())  
New_Lower_Bound_Minus_one_LSN

邀月工作室

通過以下存儲(chǔ)過程在數(shù)據(jù)庫(kù)和表級(jí)禁用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）注意，該命令同時(shí)也刪除了CDC架構(gòu)和相關(guān)的SQL代理作業(yè)。

/************** 在數(shù)據(jù)庫(kù)和表級(jí)禁用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')  
 
--當(dāng)前數(shù)據(jù)庫(kù)上禁用CDC  
EXEC sys.sp_cdc_disable_db

四、使用“更改跟蹤”以最小的磁盤開銷跟蹤凈數(shù)據(jù)更改

CDC 可以用來對(duì)數(shù)據(jù)庫(kù)和數(shù)據(jù)倉(cāng)庫(kù)的持續(xù)數(shù)據(jù)變更進(jìn)行異步數(shù)據(jù)跟蹤，而SQL Server 2008中新增的“更改跟蹤”卻是一個(gè)同步進(jìn)程，是DML操作本身（I/D/U）事務(wù)的一部分，它的最大優(yōu)勢(shì)是以最小的磁盤開銷來偵測(cè)凈行變更，它允許修改的數(shù)據(jù)以事務(wù)一致的形式表現(xiàn)，并提供了檢測(cè)數(shù)據(jù)沖突的能力。它甚至可以根據(jù)外部傳入的應(yīng)用程序上下文，來完成更細(xì)顆粒度的更改處理，參看WITH CHANGE_TRACKING_CONTEXT （http://msdn.microsoft.com/zh-cn/library/bb895330%28v=sql.100%29.aspx）

/***使用“更改跟蹤”以最小的磁盤開銷跟蹤凈數(shù)據(jù)更改****/  
/************* 3w@live.cn 邀月 **************/  
 
IF EXISTS (SELECT [name] FROM sys.databases WHERE name = 'TestDb4')  
drop DATABASE TestDb4  
Go  
CREATE DATABASE TestDb4  
GO  
 
--啟用更新跟蹤，36小時(shí)清理一次  
ALTER DATABASE TestDb4  
SET CHANGE_TRACKING = ON 
(CHANGE_RETENTION = 36 HOURS,  
AUTO_CLEANUP = ON)

注意下一步是允許快照隔離，這是微軟推祟的“最佳實(shí)踐”，盡管這樣行版本的生成會(huì)增加額外的空間使用，從而會(huì)增加總的I/O數(shù)量,但不使用快照會(huì)引發(fā)事務(wù)不一致的變更信息。

ALTER DATABASE TestDb4  
SET ALLOW_SNAPSHOT_ISOLATION ON 
GO  
 
SELECT DB_NAME(database_id) 數(shù)據(jù)庫(kù)名稱,is_auto_cleanup_on,  
retention_period,retention_period_units_desc  
FROM sys.change_tracking_databases  
/*  
數(shù)據(jù)庫(kù)名稱 is_auto_cleanup_on retention_period retention_period_units_desc  
TestDb4 1 36 HOURS  
*/  
 
USE TestDb4  
GO  
--創(chuàng)建測(cè)試表  
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)  
 
--確認(rèn)是否更新跟蹤開啟  
SELECT OBJECT_NAME(object_id) ObjNM,is_track_columns_updated_on  
FROM sys.change_tracking_tables  
 
/*  
ObjNM is_track_columns_updated_on  
DepartDemo 1  
*/  
 
--增加測(cè)試數(shù)據(jù)  
INSERT dbo.DepartDemo  
(DName,ParentID)  
VALUES 
('明教', 0),  
('五行集', 101),  
('少林派',0)  
 
SELECT * FROM dbo.DepartDemo  
 
--當(dāng)前版本  
SELECT CHANGE_TRACKING_CURRENT_VERSION ()  
as 當(dāng)前版本  
/*  
當(dāng)前版本  
1  
*/  
SELECT CHANGE_TRACKING_MIN_VALID_VERSION  
( OBJECT_ID('dbo.DepartDemo') )as 最小可用版本  
 
/*  
最小可用版本  
0  
*/

函數(shù)ChangeTable有兩種用法來檢測(cè)更改: 一、使用Changes關(guān)鍵字；二、使用Version關(guān)鍵字

/*  
一、使用Changes關(guān)鍵字  
*/  
 
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 當(dāng)前版本  
/*  
當(dāng)前版本  
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關(guān)鍵字  
*/  
 
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 當(dāng)前版本  
/*  
當(dāng)前版本  
5  
*/  
 
--跟蹤外部程序哪一部分引起的更改，這樣好找出源頭  
DECLARE @context varbinary(128) = CAST('明教內(nèi)訌引起分裂' 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 明教內(nèi)訌引起分裂  
*/

邀月工作室

小結(jié)：

本文總結(jié)了SQL Server中記錄數(shù)據(jù)變更的四個(gè)方法：觸發(fā)器、Output子句、SQL Server 2008中新增的變更數(shù)據(jù)捕獲（CDC）功能、同步更改跟蹤。其中后兩個(gè)是SQL Server 2008中新增的功能，在SQL Server 2012中更是與Always ON緊密集成。

1、不建議前兩個(gè)。

2、CDC用以實(shí)現(xiàn)異步跟蹤用戶表的數(shù)據(jù)修改，而且這一功能擁有最小的性能開銷，可以用于其他數(shù)據(jù)源的持續(xù)更新，例如將OLTP數(shù)據(jù)庫(kù)中的數(shù)據(jù)變更遷移到數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)庫(kù)。

3、”更改跟蹤”的最大優(yōu)勢(shì)是以最小的磁盤開銷來偵測(cè)凈行變更，它允許修改的數(shù)據(jù)以事務(wù)一致的形式表現(xiàn)，并提供了檢測(cè)數(shù)據(jù)沖突的能力。

其他推薦文章：

1、在VS中如何將數(shù)據(jù)同步配置為使用 SQL Server 更改跟蹤（http://msdn.microsoft.com/zh-cn/library/cc714038.aspx）

2、SQL Server 2012中復(fù)制、更改跟蹤、更改數(shù)據(jù)捕獲和 AlwaysOn 可用性組 (SQL Server)（http://msdn.microsoft.com/zh-cn/library/hh403414%28v=sql.110%29.aspx）

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

【編輯推薦】