SQL Server 2008提供了内建的方法“”变更数据捕获“”(Change Data Capture 即CDC)以实现异步跟踪用户表的数据修改,而且这一功能拥有最小的性能开销。可以用于其他数据源的持续更新,例如将OLTP数据库中的数据变更迁移到数据仓库数据库。
--开启cdc
EXEC sys.sp_cdc_enable_db
--net_changes说的是针对一行记录,如果有多个更改的话,那么以最后的一条为准。
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='frmuser',@capture_instance='frmuser',@supports_net_changes=0,@role_name=null
--确认表已经被跟踪
SELECT is_tracked_by_cdc FROM sys.tables
WHERE name = 'frmuser' and schema_id = SCHEMA_ID('dbo')
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'frmuser'
--更改表数据查询效果
UPDATE a
SET a.isvalid = 0
FROM frmuser a
WHERE Account ='sa'
SELECT TOP 50 *
FROM [cdc].[frmuser_CT]
DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , '2013-09-27 08:30')
DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , '2013-09-27 23:59:59')
--注意若在上面的日期范围内无数据的话,将提示参数不足
SELECT
CASE __$operation
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
END ,a.*
FROM [cdc].[fn_cdc_get_all_changes_frmuser]
(@FromLSN, @ToLSN, 'all') a
--撤销CDC
EXEC sys.sp_cdc_disable_table 'dbo',
'FactInternetSales','All'
EXEC sys.sp_cdc_disable_db
捕获和清除作业都是使用默认参数创建的。将立即启动捕获作业。它连续运行,每个扫描周期最多可处理 1000 个事务,并在两个周期之间停顿 5 秒钟。清除作业在每天凌晨 2 点运行一次。它将更改表项保留三天(4320 分钟),可使用单个删除语句最多删除 5000 项。