我们在浏览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')
　　insertcustomer

values('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)
　　)
　　reatefunctiondbo.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)

　　运行了上面的查询之后，插入一行，然后再一次运行这个查询。你的查询结果会显示只有一行被改动过：