注：本文章基於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
homesql2008(HOMEMAK):(0row(s)affected)
homesql2008(HOMEMAK):(1row(s)affected)
homesql2008(HOMEMAK):(1row(s)affected)
homesql2008(HOMEMAK):(1row(s)affected)
homesql2008(HOMEMAK):(1row(s)affected)
homesql2008(HOMEMAK):(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
homesql2008(HOMEMAK):(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
homesql2008(HOMEMAK):(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
homesql2008(HOMEMAK):(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
結論
為了更好的監控表結構的變化，我們可以在適當的時候關閉或開啟數據庫上的捕獲數據變化功能。
