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

　　结论

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