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

1. 準備一個數據庫，里面準備一個表，Orders

2. 啟用數據庫級別的CDC選項

--在數據庫級別啟用CDC功能

EXEC sys.sp_cdc_enable_db

這個命令執行完之后，會在系統表里面添加6個表格

3.在需要做數據捕獲的表上面啟用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 

執行之后，會有如下的輸出消息

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

與此同時，執行上面的命令還將在系統表中添加一個表格

還會添加一個函數

4.插入或者更新數據測試CDC功能

--插入或者更新數據測試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做了什么事情呢？

SELECT * FROM cdc.Orders_CT

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

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

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

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

很好理解，不是嗎？

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

--按照時間范圍查詢CDC結果

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') 

關于sys.fn_cdc_map_time_to_lsn這個函數，請參考http://msdn.microsoft.com/en-us/library/bb500137.aspx

查詢的結果如下：

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

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 

然后，每次需要用的時候，就直接調用即可。

--執行存儲過程

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

5.結合SSIS實現事實表的增量更新

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

本文所有的代碼如下：

USE SampleDatabase  
GO  
--在數據庫級別啟用CDC功能  
EXEC sys.sp_cdc_enable_db   
--在需要做數據捕獲的表格上面啟用CDC功能  
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null 
--插入或者更新數據測試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的結果  
SELECT * FROM cdc.Orders_CT  
--按照時間范圍查詢CDC結果  
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  
--執行存儲過程  
EXEC GetOrdersCDCResult '2011-8-10','2011-8-11' 

以上就是SQL Server 2008數據庫中CDC的功能使用及說明的全部內容，本文我們就介紹到這里了，希望本次的介紹能夠對您有所收獲！

【編輯推薦】

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