我們在浏覽SQL Server 2008的新特性的時候，發現有一個很有趣的特性叫Change Dada Capture。您能不能向我們詳細介紹一下怎麼利用這個新特性?
專家解答：
Change Data Capture(CDC)是SQL Server 2008的一個新特性，它可以記錄SQL Server表的插入、更新和刪除等表修改活動。利用該新特性的一個很好的例子就是對某個數據倉庫進行定期更新。我們以前需要通過使用數據裝載程序(ETL)來更新數據倉庫中所有在源系統中更改過的數據。在CDC這個新功能出現之前，我們可能只會選擇查詢源系統表裡最新更新的DATETIME列來找出那些行曾經被改動過。雖然這個方法既簡單又有效，但是它不能查找出那些行被物理刪除了。另外，我們也無法用這個辦法來確定被改動過的行改動的地方，我們只能讀取被改動過的行的當前狀態。而利用CDC，我們不僅可以輕松完成上述任務，還可以通過它來進行更復雜的對於數據修改歷史的查詢。
這裡我們簡單地介紹一下CDC，並通過編碼來演示如何來完成以下任務：
創建和配置CDC
利用CDC通過T-SQL查詢來抽取插入行、被改動過的行或者被刪除的行。
在開始講述T-SQL代碼例子之前，我們先討論一下CDC有什麼的高級功能。在執行完創建和配置步驟之後(我們會在下文講述)，CDC會開始掃描數據庫事務處理日志，查找你所指定的某些表被改動的情況，並把這些改動插入到改動表裡。CDC創建和配置過程還會創建表賦值函數，這些表賦值函數可用於查詢改動。你將利用表賦值函數，而不需要直接查詢改動表。現在我們來看一下具體例子。
下面的編碼例子只在2008年二月的CTP裡進行過測試，一些函數名稱和存儲過程的名稱現在已經有所改動。
創建和配置
CDC這項新功能可以應用在整個數據庫水平上，在默認設置裡這項功能是處於禁用狀態的。要啟用CDC，你必須是sysadmin固定服務器角色的成員。你可以在任何用戶數據庫裡啟用CDC，但是你不能在系統數據庫裡啟用該項功能。在你所選的數據庫中執行以下T-SQL腳本來啟用CDC：
以下是引用片段：
declare@rcint
exec@rc=sys.sp_cdc_enable_db
select@rc
--newcolumnaddedtosys.databases:is_cdc_enabled
selectname,is_cdc_enabledfromsys.databases
如果成功啟用CDC，存儲過程sys.sp_cdc_enable_db就會返回0值，如果失敗，就會返回!值。你可以通過在sys.databases 表裡查看新增列is_cdc_enabled 來查詢CDC是否啟用成功。如果成功啟用CDC，你會看到查詢結果為1，如果失敗了，你就會看到查詢結果為1。
下一步就是指定你想要啟用CDC的具體表。我們現在來新建一個名為customer的表：
以下是引用片段：
createtabledbo.customer
(
idintidentitynotnull
,namevarchar(50)notnull
,statevarchar(2)notnull
,constraintpk_customerprimarykeyclustered(id)
)
執行以下系統存儲過程來為customer表啟用CDC：
以下是引用片段：
execsys.sp_cdc_enable_table
@source_schema='dbo',
@source_name='customer',
@role_name='CDCRole',
@supports_net_changes=1
selectname,type,type_desc,is_tracked_by_cdcfromsys.tables
你必須是db_owner固定數據庫角色的成員才能夠執行上述的系統存儲過程，而且必須運行SQL Agent。系統存儲過程sys.sp_cdc_enable_table有許多參數，我們來一個一個介紹(只有前三個參數是必須的，其他參數是可選的，下面只列出了我們使用過的參數)：
@source_schema是你想要啟動CDC的表的Schema名稱
@source_name是你想要啟動CDC的表名稱
@role_name 是用來決定一個用戶是否能夠讀取CDC數據的數據庫角色;如果這個角色不存在，會創建出一個角色。你可以為這個角色增加用戶;你只需要增加還不是db_owner固定數據庫角色的成員的用戶到這個角色中。
@supports_net_changes 決定你是否能夠把多個改動總結到一個改動記錄裡;參數值設置為1即允許，設置為0即為不允許。
@capture_instance是你給這個CDC實例起的名稱;你可以為一個表設置兩個實例。
@index_name是用於識別源表的行的一個唯一索引的名稱;如果源表有主鍵，你可以指定NULL值。
@captured_column_list是你想要啟動CDC的列名的列表，各列名以逗號分隔;你可以指定NULL值來啟用所有列。
@filegroup_name讓你可以指定用於存儲CDC改動表的FILEGROUP。
@partition_switch讓你可以指定是否允許執行ALTER TABLE SWITCH PARTITION命令;即允許你啟用分區(TRUE或FALSE)。
在表sys.tables裡有一列名為is_tracked_by_cdc的新增列;你可以查詢它來確定某個表是否啟動了CDC。
在數據庫或表的水平上啟用CDC會在啟用了CDC的數據庫裡創建某些表、任務、存儲過程和函數。這些對象會被創建在名為cdc的schema裡，同時還會創建出一個cdc用戶。你會看到一條信息說創建了兩個SQL Agent任務：任務cdc.sql2008demo_capture會掃描數據庫事務處理日志來查詢已經啟用CDC的表，而任務cdc.sql2008demo_cleanup則會定期清除改動表。命名規則是cdc.[databasename]_task。我們會在下面的例子裡使用某些被創建的schema對象。你可以通過允許以下T-SQL腳本來查看創建的schema對象：
以下是引用片段：
selecto.name,o.type,o.type_descfromsys.objectso
joinsys.schemassons.schema_id=o.schema_id
wheres.name='cdc'
你可以通過執行以下T-SQL腳本來禁用某個表的CDC：
以下是引用片段：
execsys.sp_cdc_disable_table
@source_schema='dbo',
@source_name='customer',
@capture_instance='dbo_customer'--or'all'
你可以通過執行以下T-SQL腳本來禁用整個數據庫的CDC：
以下是引用片段：
declare@rcint
exec@rc=sys.sp_cdc_disable_db
select@rc
--showdatabasesandtheirCDCsetting
selectname,is_cdc_enabledfromsys.databases
禁用表或數據庫水平的CDC將會刪除在其啟用時創建的表、工作、存儲過程和函數等對象。
現在我們已經為一個數據庫和表啟用了CDC，我們現在來繼續看演示例子：
CDC演示
我們會通過以下步驟來演示CDC的功能：
對customer表進行一些插入、更改和刪除的活動。
顯示用來查詢改動的T-SQL代碼實例
執行以下T-SQL腳本來對customer表進行插入、更改和刪除：
以下是引用片段：
insertcustomervalues('abccompany','md')
insertcustomervalues('xyzcompany','de')
insertcustomervalues('xoxcompany','va')
updatecustomersetstate='pa'whereid=1
deletefromcustomerwhereid=3
下面的查詢會顯示上述改動的記錄：
以下是引用片段：
declare@begin_lsnbinary(10),@end_lsnbinary(10)
--getthefirstLSNforcustomerchanges
select@begin_lsn=sys.fn_cdc_get_min_lsn('dbo_customer')
--getthelastLSNforcustomerchanges
select@end_lsn=sys.fn_cdc_get_max_lsn()
--getnetchanges;groupchangesintherangebythepk
select*fromcdc.fn_cdc_get_net_changes_dbo_customer(
@begin_lsn,@end_lsn,'all');
--getindividualchangesintherange
select*fromcdc.fn_cdc_get_all_changes_dbo_customer(
@begin_lsn,@end_lsn,'all');
要抽取已經啟動CDC的表的改動，你需要提供相關的LSN。LSN是指在識別數據庫事務處理日志裡的條目的唯一日志序列號。如果這是你第一次進行查詢抽取表改動，你可以使用函數sys.fn_cdc_get_min_lsn()和函數sys.fn_cdc_get_max_lsn()來獲得最小LSN和最大LSN。如果在啟動表的CDC時，設置@supports_net_changes=1，你可以使用cdc.fn_cdc_get_net_changes_dbo_customer()來查詢淨改動。這個函數會根據你啟用CDC時指定的主鍵或唯一索引把多個改動集合到一行中。這樣你可以隨時調用cdc.fn_cdc_get_all_changes_dbo_customer()來獲取表中序列號在LSN范圍之內的任何改動。函數名稱中的dbo_customer部分是數據捕捉實例;這是默認的schema_tablename。以下是上面的查詢的結果：
第一組結果顯示淨改動;第二組結果顯示各個改動。由於我們在開始時插入了一行，又刪除了這一行，第一組結果沒有顯示這一行，因為它的序號在LSN范圍裡增添後又被刪除了;也就是說，在你聚合這些改動的時候，這一行已經不存在了。__$operation列值是：1 =刪除，2=插入，3=更改(更新前的值)，4=更改(更新後的值)。要查看更新前的值，你必須傳遞“all update old”到函數cdc.fn_cdc_get_all_changes_dbo_customer()中。__$update_mask列用於識別改動過的列。當__$operation =1或2時，所有的列都被標識為改動過。當__$Operation =3或4時，會指示出實際被改動過的列。所有的列都根據column_ordinal以二進制繪制顯示;執行存儲過程sys.sp_cdc_get_captured_columns，傳遞數據捕捉實例作為參數來查看column_ordinal值，例如：
我們現在接著用這個例子來演示如何定期抽取被改動的行。我們會新增一個表來記錄結束LSN和一個新函數來從表中獲取結束LSN。通過這樣做，我們可以獲取自上一次運行ETL過程之後的所有改動。
以下是引用片段：
createtabledbo.customer_lsn(
last_lsnbinary(10)
)
createfunctiondbo.get_last_customer_lsn()
returnsbinary(10)
as
begin
declare@last_lsnbinary(10)
select@last_lsn=last_lsnfromdbo.customer_lsn
select@last_lsn=isnull(@last_lsn,sys.fn_cdc_get_min_lsn('dbo_customer'))
return@last_lsn
end
我們會改動上面的代碼，添加函數get_last_customer_lsn()，插入或更新一行來保存結束LSN：
以下是引用片段：
declare@begin_lsnbinary(10),@end_lsnbinary(10)
--getthenextLSNforcustomerchanges
select@begin_lsn=dbo.get_last_customer_lsn()
--getthelastLSNforcustomerchanges
select@end_lsn=sys.fn_cdc_get_max_lsn()
--getthenetchanges;groupallchangesintherangebythepk
select*fromcdc.fn_cdc_get_net_changes_dbo_customer(
@begin_lsn,@end_lsn,'all');
--getallindividualchangesintherange
select*fromcdc.fn_cdc_get_all_changes_dbo_customer(
@begin_lsn,@end_lsn,'all');
--savetheend_lsninthecustomer_lsntable
updatedbo.customer_lsn
setlast_lsn=@end_lsn
if@@ROWCOUNT=0
insertintodbo.customer_lsnvalues(@end_lsn)
運行了上面的查詢之後，插入一行，然後再一次運行這個查詢。你的查詢結果會顯示只有一行被改動過：
