SQL Server2008新功能 捕获数据变化

微软介绍了SQL Server 2008的一项新功能：捕获数据变化。捕获数据变化功能可以方便地监控到表的变化。本文的第一、二部分介绍了怎样在数据库及表上开启捕获数据变化功能。下面将介绍的是，如果表的结构发生变化，捕获数据变化功能将有什么样的表现。

　　注：本文章基于SQL Server 200811月CTP。

　　第一步

　　用下面的代码创建一个名为“CDCDB”的数据库。

USE[master] 　　GO 　　/***Object:Database[CDCDB]ScriptDate:01/07/200818:46:15***/ 　　IFEXISTS(SELECTnameFROMsys.databasesWHEREname=N'CDCDB') 　　DROPDATABASE[CDCDB] 　　GO 　　USE[master] 　　GO 　　/***Object:Database[CDCDB]ScriptDate:01/07/200818:46:33***/ 　　CREATEDATABASE[CDCDB] 　　GO

　　第二步

　　创建一个名为Employee的表，代码如：

use[CDCDB] 　　go 　　/***Object:Table[dbo].[Employee]ScriptDate:01/07/200818:52:14***/ 　　IFEXISTS(SELECT*FROMsys.objects 　　WHEREobject_id=OBJECT_ID(N'[dbo].[Employee]') 　　ANDtypein(N'U')) 　　DROPTABLE[dbo].[Employee] 　　GO 　　use[CDCDB] 　　go 　　/***Object:Table[dbo].[Employee]ScriptDate:01/07/200818:52:26***/ 　　SETANSI_NULLSON 　　GO 　　SETQUOTED_IDENTIFIERON 　　GO 　　SETANSI_PADDINGON 　　GO 　　CREATETABLE[dbo].[Employee]( 　　[ID][int]NOTNULL, 　　[Name][varchar](100)NULL, 　　CONSTRAINT[Employee_PK]PRIMARYKEYCLUSTERED 　　( 　　[ID]ASC 　　)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF, 　　IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON, 　　ALLOW_PAGE_LOCKS=ON)ON[PRIMARY] 　　)ON[PRIMARY] 　　GO 　　SETANSI_PADDINGOFF 　　GO

　　第三步

　　在数据库“CDCDB”上开启捕获数据变化功能，如下：

USE[CDCDB] 　　GO 　　EXECsys.sp_cdc_enable_db_change_data_capture 　　GO

　　第四步

　　在表“Employee”上开启捕获数据变化功能，如下：

use[CDCDB] 　　go 　　GO 　　EXECsys.sp_cdc_enable_table_change_data_capture 　　@source_schema='dbo', 　　@source_name='Employee', 　　@role_name='cdc_Employee' 　　GO

第五步

　　开启了捕获数据变化功能后，在表“Employee”上增加几列，如下：

use[CDCDB] 　　go 　　GO 　　AlterTableEmployeeaddAddressvarchar(500) 　　GO 　　AlterTableEmployeeaddSalarymoney 　　GO 　　AlterTableEmployeeaddBonusmoney 　　GO

　　第六步

　　向表中插入一些数据。

use[CDCDB] 　　go 　　select*fromEmployee 　　go 　　InsertintoEmployeevalues(1,'DancingDoll','221,WestBroadst, 　　Greenbay,Wisconsin',60000,1000) 　　InsertintoEmployeevalues(2,'RainbowDance','21,Eastst, 　　Denville,NewJersey',68000,1300) 　　InsertintoEmployeevalues(3,'WaterDance','1,SouthBroadst, 　　Quincy,Massachusetts',76000,1600) 　　InsertintoEmployeevalues(4,'MickeyMouse','5,Main, 　　Greenbay,Wisconsin',120000,12000) 　　InsertintoEmployeevalues(5,'Ratyear','7,Newroad, 　　Danbury,Connecticut',45000,1600) 　　go 　　select*fromEmployee 　　go

　　执行结果

ID,Name,Address,Salary,Bonus 　　home\sql2008(HOME\MAK):(0row(s)affected) 　　home\sql2008(HOME\MAK):(1row(s)affected) 　　home\sql2008(HOME\MAK):(1row(s)affected) 　　home\sql2008(HOME\MAK):(1row(s)affected) 　　home\sql2008(HOME\MAK):(1row(s)affected) 　　home\sql2008(HOME\MAK):(1row(s)affected) 　　ID,Name,Address,Salary,Bonus 　　1,DancingDoll,221,WestBroadst, 　　Greenbay,Wisconsin,60000.0000,1000.0000 　　2,RainbowDance,21,Eastst, 　　Denville,NewJersey,68000.0000,1300.0000 　　3,WaterDance,1,SouthBroadst, 　　Quincy,Massachusetts,76000.0000,1600.0000 　　4,MickeyMouse,5,Main, 　　Greenbay,Wisconsin,120000.0000,12000.0000 　　5,Ratyear,7,Newroad, 　　Danbury,Connecticut,45000.0000,1600.0000 　　home\sql2008(HOME\MAK):(5row(s)affected)

　　第七步

　　更新及删除一些数据，如下：

use[CDCDB] 　　go 　　UpdateEmployeesetname='test'whereid=5 　　go 　　DeleteEmployeewhereidin(3,4) 　　Go

　　第八步

　　通过以下查询语句查看DDL和DML的变化。[参考图 Fig 1.0 and Fig 1.1]

use[CDCDB] 　　go 　　select*fromcdc.ddl_history 　　go

　　执行结果

　　source_object_id,object_id,required_column_update,ddl_command,ddl_lsn,ddl_time
　　565577053,597577167,0,AlterTableEmployeeaddAddressvarchar(500)
　　,0x000000360000006B0022,2008-02-0915:03:00.000
　　565577053,597577167,0,AlterTableEmployeeaddSalarymoney
　　,0x000000360000007A0018,2008-02-0915:03:00.000
　　565577053,597577167,0,AlterTableEmployeeaddBonusmoney
　　,0x00000036000000800018,2008-02-0915:03:00.000
　　home\sql2008(HOME\MAK):(3row(s)affected)

　　图Fig 1.0

use[CDCDB] 　　go 　　Selectcase__$operationwhen1then'Deleting' 　　when2then'Inserting' 　　when3then'ValuebeforeUpdate' 　　when4then'ValueafterUpdate' 　　when5then'Merge'end,__$update_mask,ID,Name 　　fromcdc.dbo_Employee_CT 　　go

　　执行结果

,__$update_mask,ID,Name 　　Inserting,0x03,1,DancingDoll 　　Inserting,0x03,2,RainbowDance 　　Inserting,0x03,3,WaterDance 　　Inserting,0x03,4,MickeyMouse 　　Inserting,0x03,5,Ratyear 　　ValuebeforeUpdate,0x02,5,Ratyear 　　ValueafterUpdate,0x02,5,test 　　Deleting,0x03,3,WaterDance 　　Deleting,0x03,4,MickeyMouse 　　home\sql2008(HOME\MAK):(9row(s)affected)

　　图Fig 1.1

　　从以上的结果中，我们可以看出只有ID和Name这2列被监控到了，开启数据捕获功能之后增加的所有列都内有被监控到。

　　

　　第九步

　　现在我们对所有列进行数据捕获。先关闭当前的数据捕获功能，再打开数据捕获功能就可以了。使用下面的语句关闭当前功能：

use[CDCDB] 　　go 　　EXECsys.sp_cdc_disable_table_change_data_capture 　　@source_schema='dbo', 　　@source_name='Employee', 　　@capture_instance='dbo_Employee' 　　Go

　　第十步

　　使用如下T-SQL语句在表“Employee”上开启捕获数据变化功能。这次我们想查看到哪些列被监控了。

use[CDCDB] 　　go 　　EXECsys.sp_cdc_enable_table_change_data_capture 　　@source_schema='dbo', 　　@source_name='Employee', 　　@role_name='cdc_Employee', 　　@captured_column_list=N'ID,Name,Salary,Bonus' 　　GO

　　查询捕获数据变化模式下Empolyee表的变化，如下：

use[CDCDB] 　　go 　　select*fromcdc.dbo_Employee_CT 　　go

　　执行结果显示，Empolyee表的所有列都被监控了。(参考图 Fig 1.2)

　　图 Fig 1.2

　　结论

　　为了更好的监控表结构的变化，我们可以在适当的时候关闭或开启数据库上的捕获数据变化功能。