SQL Server 2008 - Change Data Capture - （第三部分）SQL Server 2008 - Change Data Capture - （第三部分）
--王成辉翻译整理，转贴请注明注册微软BI开拓者http://www.windbi.com/
--原帖地址
微软在SQL Server 2008里引入了Change Data Capture这个特点来跟踪对表所做的更改。在第1篇文章和第2篇文章里演示了怎样启用数据库和表上的Change Data Capture特点。
本文将演示表结构改变时Change Data Capture会发生的事。
注意：本文是针对SQLServer2008的11月的CTP版来写的。
步骤1
创建数据库CDCDB，如下：
USE [master]
GO
/*** Object: Database [CDCDB] Script Date: 01/07/2008 18:46:15 ***/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CDCDB')
DROP DATABASE [CDCDB]
GO
USE [master]
GO
/*** Object: Database [CDCDB] Script Date: 01/07/2008 18:46:33 ***/
CREATE DATABASE [CDCDB]
GO
步骤2
use [CDCDB]
go
/*** Object: Table [dbo].[Employee] Script Date: 01/07/2008 18:52:14 ***/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Employee]')
AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO
use [CDCDB]
go
/*** Object: Table [dbo].[Employee] Script Date: 01/07/2008 18:52:26 ***/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [Employee_PK] PRIMARY KEY CLUSTERED
(
[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
SET ANSI_PADDING OFF
GO
步骤3
启用数据库CDCDB上的Change Data Capture，如下：
USE [CDCDB]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO
步骤4
现在启用数据库CDCDB里的表Employee上的Change Data Capture，如下：
use [CDCDB]
go
GO
EXEC sys.sp_cdc_enable_table_change_data_capture
@source_schema = 'dbo',
@source_name = 'Employee',
@role_name = 'cdc_Employee'
GO
步骤5
在启用表上的CDC之后，给表Employee添加几列，如下：
use [CDCDB]
go
GO
Alter Table Employee add Address varchar(500)
GO
Alter Table Employee add Salary money
GO
Alter Table Employee add Bonus money
GO
步骤6
给表添加一些数据：
use [CDCDB]
go
select * from Employee
go
Insert into Employee values (1, 'Dancing Doll','221, West Broad st,
Greenbay, Wisconsin',60000,1000)
Insert into Employee values (2, 'Rainbow Dance','21, East st,
Denville, New Jersey',68000,1300)
Insert into Employee values (3, 'Water Dance','1, South Broad st,
Quincy, Massachusetts',76000,1600)
Insert into Employee values (4, 'Mickey Mouse','5, Main, Greenbay,
Wisconsin',120000,12000)
Insert into Employee values (5, 'Rat year','7, New road, Danbury ,
Connecticut',45000,1600)
go
select * from Employee
go
结果
ID, Name, Address, Salary, Bonus
home\sql2008(HOME\MAK): (0 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)
ID, Name, Address, Salary, Bonus
1, Dancing Doll, 221, West Broad st, Greenbay, Wisconsin, 60000.0000, 1000.0000
2, Rainbow Dance, 21, East st, Denville, New Jersey, 68000.0000, 1300.0000
3, Water Dance, 1, South Broad st, Quincy, Massachusetts, 76000.0000, 1600.0000
4, Mickey Mouse, 5, Main, Greenbay, Wisconsin, 120000.0000, 12000.0000
5, Rat year, 7, New road, Danbury , Connecticut, 45000.0000, 1600.0000
home\sql2008(HOME\MAK): (5 row(s) affected)
步骤7
更新并删除一些数据，如下：
use [CDCDB]
go
Update Employee set name='test' where id =5
go
Delete Employee where id in (3,4)
Go
步骤8
要看跟踪到的DDL和DML所做的更改，执行下面的查询：
use [CDCDB]
go
select * from cdc.ddl_history
go
结果
source_object_id, object_id, required_column_update, ddl_command, ddl_lsn, ddl_time
565577053, 597577167, 0, Alter Table Employee add Address varchar(500), 0x000000360000006B0022, 2008-02-09 15:03:00.000
565577053, 597577167, 0, Alter Table Employee add Salary money, 0x000000360000007A0018, 2008-02-09 15:03:00.000
565577053, 597577167, 0, Alter Table Employee add Bonus money, 0x00000036000000800018, 2008-02-09 15:03:00.000
home\sql2008(HOME\MAK): (3 row(s) affected)
Fig 1.0
use [CDCDB]
go
Select case __$operation when 1 then 'Deleting'
when 2 then 'Inserting'
when 3 then 'Value before Update'
when 4 then 'Value after Update'
when 5 then 'Merge' end ,__$update_mask,ID,Name
from cdc.dbo_Employee_CT
go
结果
, __$update_mask, ID, Name
Inserting, 0x03, 1, Dancing Doll
Inserting, 0x03, 2, Rainbow Dance
.Inserting, 0x03, 3, Water Dance
Inserting, 0x03, 4, Mickey Mouse
Inserting, 0x03, 5, Rat year
Value before Update, 0x02, 5, Rat year
Value after Update, 0x02, 5, test
Deleting, 0x03, 3, Water Dance
Deleting, 0x03, 4, Mickey Mouse
home\sql2008(HOME\MAK): (9 row(s) affected)
 Fig 1.1
从上面的结果可以看到，仅跟踪了ID和Name列。在启用Change Data Capture后在表上添加的任何列都没被跟踪。
步骤9
现在给Change Data Capture添加所有的列。这可以通过禁用目前的Change Data Capture并用新列启动它来实现。使用下面的SQL语句禁用Change Data Capture：
use [CDCDB]
go
EXEC sys.sp_cdc_disable_table_change_data_capture
@source_schema = 'dbo',
@source_name = 'Employee',
@capture_instance = 'dbo_Employee'
Go
步骤10
现在启用Employee表上的Change Data Capture。这次我们明确指定要跟踪的列：
use [CDCDB]
go
EXEC sys.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
查询cdc Employee 变化表，如下：
use [CDCDB]
go
select * from cdc.dbo_Employee_CT
go
结果显示employee表的所有列都正被跟踪。
Fig 1.2
小结
这第3篇文章演示了怎样禁用并启用表上的Change Data Capture，以便能及时跟踪对表结构所做的更改。
|