SQL Server 2008數(shù)據(jù)庫中CDC的功能使用及說明的相關(guān)知識是本文我們主要要介紹的內(nèi)容，那么什么是CDC呢？CDC（Change Data Capture:變更數(shù)據(jù)捕獲）這個功能是SQL Server 2008企業(yè)版的功能，它提供了一種新的機制，對表格數(shù)據(jù)的更新進行跟蹤，在數(shù)據(jù)倉庫的建設(shè)過程中，通過這種技術(shù)，可以簡化從業(yè)務(wù)數(shù)據(jù)庫導(dǎo)入數(shù)據(jù)的復(fù)雜度。

1. 準(zhǔn)備一個數(shù)據(jù)庫，里面準(zhǔn)備一個表，Orders

2. 啟用數(shù)據(jù)庫級別的CDC選項

--在數(shù)據(jù)庫級別啟用CDC功能

EXEC sys.sp_cdc_enable_db

這個命令執(zhí)行完之后，會在系統(tǒng)表里面添加6個表格

3.在需要做數(shù)據(jù)捕獲的表上面啟用CDC選項

EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null 
http://msdn.microsoft.com/en-us/library/bb522475.aspx 

執(zhí)行之后，會有如下的輸出消息

這個提示的意思是說，要啟動SQL Server Agent。因為CDC功能是要通過一個兩個作業(yè)來自動化完成的

與此同時，執(zhí)行上面的命令還將在系統(tǒng)表中添加一個表格

還會添加一個函數(shù)

4.插入或者更新數(shù)據(jù)測試CDC功能

--插入或者更新數(shù)據(jù)測試CDC功能

INSERT Orders(CustomerID) VALUES('Microsoft');  
INSERT Orders(CustomerID) VALUES('Google');  
UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 
DELETE FROM Orders WHERE OrderID=2 

這個范例插入兩行數(shù)據(jù)，緊接著又對第一行更新，然后還刪除了第二行，所以最終只有一行數(shù)據(jù)

那么，我們來看看CDC做了什么事情呢？

SELECT * FROM cdc.Orders_CT

我們可以來解釋一下上面結(jié)果的含義：

__$operation=2的情況，表示新增

__$operation=3或者4，表示更新，3表示舊值，4表示新值

__$operation=1的情況，表示刪除

很好理解，不是嗎？

但是，我們一般都是需要按照時間范圍進行檢索，對吧，所以，需要使用下面的語法進行查詢

--按照時間范圍查詢CDC結(jié)果

DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
DECLARE @start_time DATETIME = '2011-8-10 00:00:00' 
DECLARE @end_time DATETIME ='2011-8-11 00:00:00' 
SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') 

關(guān)于sys.fn_cdc_map_time_to_lsn這個函數(shù)，請參考http://msdn.microsoft.com/en-us/library/bb500137.aspx

查詢的結(jié)果如下：

如果需要包含更新操作的舊值，則可以以下的語法

DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
DECLARE @start_time DATETIME = '2011-8-10 00:00:00' 
DECLARE @end_time DATETIME ='2011-8-11 00:00:00' 
SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all update old') 

通常，為了方便起見，我們會將這個查詢定義為一個存儲過程，如下

--定義存儲過程來進行查詢

CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME)  
AS  
BEGIN  
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')  
END 

然后，每次需要用的時候，就直接調(diào)用即可。

--執(zhí)行存儲過程

EXEC GetOrdersCDCResult '2011-8-10','2011-8-11'

5.結(jié)合SSIS實現(xiàn)事實表的增量更新

下面展示了一個SSIS 包的設(shè)計，這里面讀取CDC的數(shù)據(jù)，先進行一些查找，然后按照__$operation的值拆分成為三個操作，分別進行插入，更新和刪除，這樣就可以實現(xiàn)對事實表的增量更新

本文所有的代碼如下：

USE SampleDatabase  
GO  
--在數(shù)據(jù)庫級別啟用CDC功能  
EXEC sys.sp_cdc_enable_db   
--在需要做數(shù)據(jù)捕獲的表格上面啟用CDC功能  
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null 
--插入或者更新數(shù)據(jù)測試CDC功能  
INSERT Orders(CustomerID) VALUES('Microsoft');  
INSERT Orders(CustomerID) VALUES('Google');  
UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1 
DELETE FROM Orders WHERE OrderID=2 
--查詢CDC的結(jié)果  
SELECT * FROM cdc.Orders_CT  
--按照時間范圍查詢CDC結(jié)果  
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
DECLARE @start_time DATETIME = '2011-8-10 00:00:00' 
DECLARE @end_time DATETIME ='2011-8-11 00:00:00' 
SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')  
--定義存儲過程來進行查詢  
CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME)  
AS  
BEGIN  
DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10)  
SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)  
SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time)  
SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')  
END  
--執(zhí)行存儲過程  
EXEC GetOrdersCDCResult '2011-8-10','2011-8-11' 

以上就是SQL Server 2008數(shù)據(jù)庫中CDC的功能使用及說明的全部內(nèi)容，本文我們就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

【編輯推薦】

1. SQL Server數(shù)據(jù)庫鏈接查詢的方式詳解
2. SQL Server 2005導(dǎo)入Oracle 10g的C#源碼
3. SQL Server數(shù)據(jù)庫沒有JOIN條件導(dǎo)致笛卡爾乘積
4. SQL Server 2008中使用FileStream存取大文件實例介紹
5. SQL Server數(shù)據(jù)庫row_number() over() 來自動產(chǎn)生行號