SQLServer2008数据库中CDC的功能使用及说明

SQL Server 2008数据库中CDC的功能使用及说明的相关知识是本文我们主要要介绍的内容，那么什么是CDC呢？CDC（Change Data Capture:变更数据捕获）这个功能是SQL Server 2008企业版的功能，它提供了一种新的机制，对表格数据的更新进行跟踪，在数据仓库的建设过程中，通过这种技术，可以简化从业务数据库导入数据的复杂度。

剑河网站制作公司哪家好，找创新互联！从网页设计、网站建设、微信开发、APP开发、响应式网站设计等网站项目制作，到程序开发，运营维护。创新互联公司2013年成立到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联。

1. 准备一个数据库，里面准备一个表，Orders

2. 启用数据库级别的CDC选项

--在数据库级别启用CDC功能

EXEC sys.sp_cdc_enable_db

这个命令执行完之后，会在系统表里面添加6个表格

3.在需要做数据捕获的表上面启用CDC选项

 
 
 
 
  
  
  
  EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null
  
  
  
  http://msdn.microsoft.com/en-us/library/bb522475.aspx

执行之后，会有如下的输出消息

这个提示的意思是说，要启动SQL Server Agent。因为CDC功能是要通过一个两个作业来自动化完成的

与此同时，执行上面的命令还将在系统表中添加一个表格

还会添加一个函数

4.插入或者更新数据测试CDC功能

--插入或者更新数据测试CDC功能

 
 
 
 
  
  
  
  INSERT Orders(CustomerID) VALUES('Microsoft'); 
  
  
  
  INSERT Orders(CustomerID) VALUES('Google'); 
  
  
  
  UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1
  
  
  
  DELETE FROM Orders WHERE OrderID=2

这个范例插入两行数据，紧接着又对第一行更新，然后还删除了第二行，所以最终只有一行数据

那么，我们来看看CDC做了什么事情呢？

SELECT * FROM cdc.Orders_CT

我们可以来解释一下上面结果的含义：

__$operation=2的情况，表示新增

__$operation=3或者4，表示更新，3表示旧值，4表示新值

__$operation=1的情况，表示删除

很好理解，不是吗？

但是，我们一般都是需要按照时间范围进行检索，对吧，所以，需要使用下面的语法进行查询

--按照时间范围查询CDC结果

 
 
 
 
  
  
  
  DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) 
  
  
  
  DECLARE @start_time DATETIME = '2011-8-10 00:00:00'
  
  
  
  DECLARE @end_time DATETIME ='2011-8-11 00:00:00'
  
  
  
  SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) 
  
  
  
  SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) 
  
  
  
  SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all')

关于sys.fn_cdc_map_time_to_lsn这个函数，请参考http://msdn.microsoft.com/en-us/library/bb500137.aspx

查询的结果如下：

如果需要包含更新操作的旧值，则可以以下的语法

 
 
 
 
  
  
  
  DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) 
  
  
  
  DECLARE @start_time DATETIME = '2011-8-10 00:00:00'
  
  
  
  DECLARE @end_time DATETIME ='2011-8-11 00:00:00'
  
  
  
  SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) 
  
  
  
  SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) 
  
  
  
  SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all update old')

通常，为了方便起见，我们会将这个查询定义为一个存储过程，如下

--定义存储过程来进行查询

 
 
 
 
  
  
  
  CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME) 
  
  
  
  AS 
  
  
  
  BEGIN 
  
  
  
  DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) 
  
  
  
  SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) 
  
  
  
  SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) 
  
  
  
  SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') 
  
  
  
  END

然后，每次需要用的时候，就直接调用即可。

--执行存储过程

EXEC GetOrdersCDCResult '2011-8-10','2011-8-11'

5.结合SSIS实现事实表的增量更新

下面展示了一个SSIS 包的设计，这里面读取CDC的数据，先进行一些查找，然后按照__$operation的值拆分成为三个操作，分别进行插入，更新和删除，这样就可以实现对事实表的增量更新

本文所有的代码如下：

 
 
 
 
  
  
  
  USE SampleDatabase 
  
  
  
  GO 
  
  
  
  --在数据库级别启用CDC功能 
  
  
  
  EXEC sys.sp_cdc_enable_db  
  
  
  
  --在需要做数据捕获的表格上面启用CDC功能 
  
  
  
  EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='Orders',@capture_instance='Orders',@supports_net_changes=0,@role_name=null
  
  
  
  --插入或者更新数据测试CDC功能 
  
  
  
  INSERT Orders(CustomerID) VALUES('Microsoft'); 
  
  
  
  INSERT Orders(CustomerID) VALUES('Google'); 
  
  
  
  UPDATE Orders SET CustomerID='Yahoo' WHERE OrderID=1
  
  
  
  DELETE FROM Orders WHERE OrderID=2
  
  
  
  --查询CDC的结果 
  
  
  
  SELECT * FROM cdc.Orders_CT 
  
  
  
  --按照时间范围查询CDC结果 
  
  
  
  DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) 
  
  
  
  DECLARE @start_time DATETIME = '2011-8-10 00:00:00'
  
  
  
  DECLARE @end_time DATETIME ='2011-8-11 00:00:00'
  
  
  
  SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) 
  
  
  
  SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) 
  
  
  
  SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') 
  
  
  
  --定义存储过程来进行查询 
  
  
  
  CREATE PROC GetOrdersCDCResult(@start_time DATETIME,@end_time DATETIME) 
  
  
  
  AS 
  
  
  
  BEGIN 
  
  
  
  DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) 
  
  
  
  SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time) 
  
  
  
  SELECT @end_lsn=sys.fn_cdc_map_time_to_lsn(' largest less than or equal',@end_time) 
  
  
  
  SELECT * FROM cdc.fn_cdc_get_all_changes_Orders(@from_lsn,@end_lsn,'all') 
  
  
  
  END 
  
  
  
  --执行存储过程 
  
  
  
  EXEC GetOrdersCDCResult '2011-8-10','2011-8-11'

以上就是SQL Server 2008数据库中CDC的功能使用及说明的全部内容，本文我们就介绍到这里了，希望本次的介绍能够对您有所收获！

分享名称：SQLServer2008数据库中CDC的功能使用及说明
本文链接：http://www.mswzjz.cn/qtweb/news44/267344.html

攀枝花网站建设、攀枝花网站运维推广公司-贝锐智能，是专注品牌与效果的网络营销公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：贝锐智能

贝锐智能技术为您推荐以下文章