本站业务范围:1、PC端软件开发、网站开发 2、移动端APP、网站、微信接口、微商城开发 3、视频教程、课程设计和辅导 4、单片机开发 5、串口通讯调试
 当前位置:文章中心 >> sql2016_sql2012_sql2015_sql2008
立即购买视频教程 SQL Server 2008新功能
夜鹰教程网 来源:www.yyjcw.com 日期:2016-10-23 13:31:41
我们在浏览SQL Server 2008的新特性的时候,发现有一个很有趣的特性叫Change Dada Capture。您能不能向我们详细介绍一下怎么利用这个新特性?

这篇文章不能解决你的问题?我们还有相关视频教程云课堂 全套前端开发工程师培训课程

微信号:yyjcw10000 QQ:1416759661  远程协助需要加QQ!

业务范围:视频教程|程序开发|在线解答|Demo制作|远程调试| 点击查看相关的视频教程

技术范围:全端开发/前端开发/webapp/web服务/接口开发/单片机/C#/java/node/sql server/mysql/mongodb/android/。 



问题:

  我们在浏览SQL Server 2008的新特性的时候,发现有一个很有趣的特性叫Change Dada Capture。您能不能向我们详细介绍一下怎么利用这个新特性?

  专家解答:

  Change Data Capture(CDC)是SQL Server 2008的一个新特性,它可以记录SQL Server表的插入、更新和删除等表修改活动。利用该新特性的一个很好的例子就是对某个数据仓库进行定期更新。我们以前需要通过使用数据装载程序(ETL)来更新数据仓库中所有在源系统中更改过的数据。在CDC这个新功能出现之前,我们可能只会选择查询源系统表里最新更新的DATETIME列来找出那些行曾经被改动过。虽然这个方法既简单又有效,但是它不能查找出那些行被物理删除了。另外,我们也无法用这个办法来确定被改动过的行改动的地方,我们只能读取被改动过的行的当前状态。而利用CDC,我们不仅可以轻松完成上述任务,还可以通过它来进行更复杂的对于数据修改历史的查询。

  这里我们简单地介绍一下CDC,并通过编码来演示如何来完成以下任务:

  创建和配置CDC

  利用CDC通过T-SQL查询来抽取插入行、被改动过的行或者被删除的行。

  在开始讲述T-SQL代码例子之前,我们先讨论一下CDC有什么的高级功能。在执行完创建和配置步骤之后(我们会在下文讲述),CDC会开始扫描数据库事务处理日志,查找你所指定的某些表被改动的情况,并把这些改动插入到改动表里。CDC创建和配置过程还会创建表赋值函数,这些表赋值函数可用于查询改动。你将利用表赋值函数,而不需要直接查询改动表。现在我们来看一下具体例子。

  下面的编码例子只在2008年二月的CTP里进行过测试,一些函数名称和存储过程的名称现在已经有所改动。

  创建和配置

  CDC这项新功能可以应用在整个数据库水平上,在默认设置里这项功能是处于禁用状态的。要启用CDC,你必须是sysadmin固定服务器角色的成员。你可以在任何用户数据库里启用CDC,但是你不能在系统数据库里启用该项功能。在你所选的数据库中执行以下T-SQL脚本来启用CDC:

 

以下是引用片段:
  declare@rcint
  exec@rc=sys.sp_cdc_enable_db
  select@rc
  --newcolumnaddedtosys.databases:is_cdc_enabled
  selectname,is_cdc_enabledfromsys.databases

  如果成功启用CDC,存储过程sys.sp_cdc_enable_db就会返回0值,如果失败,就会返回!值。你可以通过在sys.databases 表里查看新增列is_cdc_enabled 来查询CDC是否启用成功。如果成功启用CDC,你会看到查询结果为1,如果失败了,你就会看到查询结果为1。

  下一步就是指定你想要启用CDC的具体表。我们现在来新建一个名为customer的表:

以下是引用片段:
  createtabledbo.customer
  (
  idintidentitynotnull
  ,namevarchar(50)notnull
  ,statevarchar(2)notnull
  ,constraintpk_customerprimarykeyclustered(id)
  )

  执行以下系统存储过程来为customer表启用CDC:

以下是引用片段:
  execsys.sp_cdc_enable_table
  @source_schema='dbo',
  @source_name='customer',
  @role_name='CDCRole',
  @supports_net_changes=1
  selectname,type,type_desc,is_tracked_by_cdcfromsys.tables

  你必须是db_owner固定数据库角色的成员才能够执行上述的系统存储过程,而且必须运行SQL Agent。系统存储过程sys.sp_cdc_enable_table有许多参数,我们来一个一个介绍(只有前三个参数是必须的,其他参数是可选的,下面只列出了我们使用过的参数):

 

  @source_schema是你想要启动CDC的表的Schema名称

 

  @source_name是你想要启动CDC的表名称

  @role_name 是用来决定一个用户是否能够读取CDC数据的数据库角色;如果这个角色不存在,会创建出一个角色。你可以为这个角色增加用户;你只需要增加还不是db_owner固定数据库角色的成员的用户到这个角色中。

  @supports_net_changes 决定你是否能够把多个改动总结到一个改动记录里;参数值设置为1即允许,设置为0即为不允许。

  @capture_instance是你给这个CDC实例起的名称;你可以为一个表设置两个实例。

  @index_name是用于识别源表的行的一个唯一索引的名称;如果源表有主键,你可以指定NULL值。

  @captured_column_list是你想要启动CDC的列名的列表,各列名以逗号分隔;你可以指定NULL值来启用所有列。

  @filegroup_name让你可以指定用于存储CDC改动表的FILEGROUP。

  @partition_switch让你可以指定是否允许执行ALTER TABLE SWITCH PARTITION命令;即允许你启用分区(TRUE或FALSE)。

  在表sys.tables里有一列名为is_tracked_by_cdc的新增列;你可以查询它来确定某个表是否启动了CDC。

  在数据库或表的水平上启用CDC会在启用了CDC的数据库里创建某些表、任务、存储过程和函数。这些对象会被创建在名为cdc的schema里,同时还会创建出一个cdc用户。你会看到一条信息说创建了两个SQL Agent任务:任务cdc.sql2008demo_capture会扫描数据库事务处理日志来查询已经启用CDC的表,而任务cdc.sql2008demo_cleanup则会定期清除改动表。命名规则是cdc.[databasename]_task。我们会在下面的例子里使用某些被创建的schema对象。你可以通过允许以下T-SQL脚本来查看创建的schema对象:

以下是引用片段:
  selecto.name,o.type,o.type_descfromsys.objectso
  joinsys.schemassons.schema_id=o.schema_id
  wheres.name='cdc'

 

  你可以通过执行以下T-SQL脚本来禁用某个表的CDC:

以下是引用片段:
  execsys.sp_cdc_disable_table
  @source_schema='dbo',
  @source_name='customer',
  @capture_instance='dbo_customer'--or'all'

  你可以通过执行以下T-SQL脚本来禁用整个数据库的CDC:

以下是引用片段:
  declare@rcint
  exec@rc=sys.sp_cdc_disable_db
  select@rc
  --showdatabasesandtheirCDCsetting
  selectname,is_cdc_enabledfromsys.databases

  禁用表或数据库水平的CDC将会删除在其启用时创建的表、工作、存储过程和函数等对象。

  现在我们已经为一个数据库和表启用了CDC,我们现在来继续看演示例子:

  CDC演示

  我们会通过以下步骤来演示CDC的功能:

  对customer表进行一些插入、更改和删除的活动。

  显示用来查询改动的T-SQL代码实例

  执行以下T-SQL脚本来对customer表进行插入、更改和删除:

以下是引用片段:
  insertcustomervalues('abccompany','md')
  insertcustomervalues('xyzcompany','de')
  insertcustomer

values('xoxcompany','va')
  updatecustomersetstate='pa'whereid=1
  deletefromcustomerwhereid=3

 

  下面的查询会显示上述改动的记录:

以下是引用片段:
  declare@begin_lsnbinary(10),@end_lsnbinary(10)
  --getthefirstLSNforcustomerchanges
  select@begin_lsn=sys.fn_cdc_get_min_lsn('dbo_customer')
  --getthelastLSNforcustomerchanges
  select@end_lsn=sys.fn_cdc_get_max_lsn()
  --getnetchanges;groupchangesintherangebythepk
  select*fromcdc.fn_cdc_get_net_changes_dbo_customer(
  @begin_lsn,@end_lsn,'all');
  --getindividualchangesintherange
  select*fromcdc.fn_cdc_get_all_changes_dbo_customer(
  @begin_lsn,@end_lsn,'all');

 

  要抽取已经启动CDC的表的改动,你需要提供相关的LSN。LSN是指在识别数据库事务处理日志里的条目的唯一日志序列号。如果这是你第一次进行查询抽取表改动,你可以使用函数sys.fn_cdc_get_min_lsn()和函数sys.fn_cdc_get_max_lsn()来获得最小LSN和最大LSN。如果在启动表的CDC时,设置@supports_net_changes=1,你可以使用cdc.fn_cdc_get_net_changes_dbo_customer()来查询净改动。这个函数会根据你启用CDC时指定的主键或唯一索引把多个改动集合到一行中。这样你可以随时调用cdc.fn_cdc_get_all_changes_dbo_customer()来获取表中序列号在LSN范围之内的任何改动。函数名称中的dbo_customer部分是数据捕捉实例;这是默认的schema_tablename。以下是上面的查询的结果: 

  第一组结果显示净改动;第二组结果显示各个改动。由于我们在开始时插入了一行,又删除了这一行,第一组结果没有显示这一行,因为它的序号在LSN范围里增添后又被删除了;也就是说,在你聚合这些改动的时候,这一行已经不存在了。__$operation列值是:1 =删除,2=插入,3=更改(更新前的值),4=更改(更新后的值)。要查看更新前的值,你必须传递“all update old”到函数cdc.fn_cdc_get_all_changes_dbo_customer()中。__$update_mask列用于识别改动过的列。当__$operation =1或2时,所有的列都被标识为改动过。当__$operation =3或4时,会指示出实际被改动过的列。所有的列都根据column_ordinal以二进制绘制显示;执行存储过程sys.sp_cdc_get_captured_columns,传递数据捕捉实例作为参数来查看column_ordinal值,例如: 

 

  我们现在接着用这个例子来演示如何定期抽取被改动的行。我们会新增一个表来记录结束LSN和一个新函数来从表中获取结束LSN。通过这样做,我们可以获取自上一次运行ETL过程之后的所有改动。

以下是引用片段:
  createtabledbo.customer_lsn(
  last_lsnbinary(10)
  )
  reatefunctiondbo.get_last_customer_lsn()
  returnsbinary(10)
  as
  begin
  declare@last_lsnbinary(10)
  select@last_lsn=last_lsnfromdbo.customer_lsn
  select@last_lsn=isnull(@last_lsn,sys.fn_cdc_get_min_lsn('dbo_customer'))
  return@last_lsn
  end

 

  我们会改动上面的代码,添加函数get_last_customer_lsn(),插入或更新一行来保存结束LSN:

以下是引用片段:
  declare@begin_lsnbinary(10),@end_lsnbinary(10)
  --getthenextLSNforcustomerchanges
  select@begin_lsn=dbo.get_last_customer_lsn()
  --getthelastLSNforcustomerchanges
  select@end_lsn=sys.fn_cdc_get_max_lsn()
  --getthenetchanges;groupallchangesintherangebythepk
  select*fromcdc.fn_cdc_get_net_changes_dbo_customer(
  @begin_lsn,@end_lsn,'all');
  --getallindividualchangesintherange
  select*fromcdc.fn_cdc_get_all_changes_dbo_customer(
  @begin_lsn,@end_lsn,'all');
  --savetheend_lsninthecustomer_lsntable
  updatedbo.customer_lsn
  setlast_lsn=@end_lsn
  if@@ROWCOUNT=0
  insertintodbo.customer_lsnvalues(@end_lsn)

 

  运行了上面的查询之后,插入一行,然后再一次运行这个查询。你的查询结果会显示只有一行被改动过: 

 

复制链接 网友评论 收藏本文 关闭此页
上一条: SQL Server 2008怎样满足下一代数…  下一条: sql2008序列号
夜鹰教程网成立于2008年,目前已经运营了将近 13 年,发布了大量关于 html5/css3/C#/asp.net/java/python/nodejs/mongodb/sql server/android/javascript/mysql/mvc/easyui/vue/echarts原创教程。 我们一直都在坚持的是:认证负责、一丝不苟、以工匠的精神来打磨每一套教程,让读者感受到作者的用心。我们默默投入的时间,确保每一套教程都是一件作品,而不是呆板的文字和视频! 目前我们推出在线辅导班试运营,模式为一对一辅导,教学工具为QQ。我们的辅导学科包括 java 、android原生开发、webapp开发、商城开发、C#和asp.net开发,winform和物联网开发、web前端开发,但不仅限于此。 普通班针对的是国内学员,例如想打好基础的大学生、想转行的有志青年、想深入学习的程序员、想开发软件的初学者或者业余爱好者等。 就业办针对即将毕业上岗的大四学生,或者打算转行的初级开发工程师。 留学生班针对的是在欧美、加拿大、澳洲、日本、韩国、新加坡等地留学的中国学子,目的是让大家熟练地掌握编程技能,按时完成老师布置的作业,并能顺利地通过考试。 详细咨询QQ:1416759661   夜鹰教程网  基于角色的权限管理系统(c-s/b-s)。
  夜鹰教程网  基于nodejs的聊天室开发视频教程
  夜鹰教程网  Git分布式版本管理视频教程
  夜鹰教程网  MVC+EasyUI视频教程
  夜鹰教程网  在线考试系统视频教程
  夜鹰教程网  MongoDB视频教程。
  夜鹰教程网 Canvas视频教程
  夜鹰教程网 报表开发视频教程
  推荐教程/优惠活动

  热门服务/教程目录

  夜鹰教程网  新手必看,详细又全面。
  夜鹰教程网  购买教程  夜鹰教程网  在线支付-方便
  夜鹰教程网  担保交易-快捷安全   夜鹰教程网  闪电发货
  夜鹰教程网  电话和QQ随时可以联系我们。
  夜鹰教程网 不会的功能都可以找我们,按工作量收费。

客服电话:153 9760 0032

购买教程QQ:1416759661  
  热点推荐
牛人是怎样处理海量数据的
MYSQL与MSSQL UPDATE两表更新语法…
SQL SERVER常用日期函数的详解
随机查询ORDER BY NEWID()
批量插入数据 延时 SQL
用sql转换汉字为拼音
定时同步服务器上的数据
SQL Server 2008与SQL Server 200…
在XP下安装SQL Server2000 清除安…
SQL触发器实例
Mssql触发器学习例子之UPDATE触发…
两表连接的SQL语句
写一个HTML代码或C++代码分析器是…
分页存储过程!
普通行列转换 SQL(1)
  尊贵服务
夜鹰教程网 承接业务:软件开发 网站开发 网页设计 .Net+C#+VS2008+MSsql+Jquery+ExtJs全套高清完整版视频教程
  最近更新
SQL命令:增加字段、修改字段、修…
sql 中的 indexOf 与 lastIndexOf…
SQL中使用正则表达式提取或者过滤…
什么是BI?
Mongodb是一种比较常见的NOSQL数据…
SQL2008 提示评估期已过的解决方法…
存在属性(Directory, Compressed)…
属性不匹配directory,archive
sql2012安装图解教程
一条sql语句批量插入多条记录
sql2016下载
sql server2008 r2 密钥
sql2016安装密钥
sql2016安装图解教程
sql server 2012提示评估期已过的…
  工具下载  需要远程协助? 

sql2008视频教程 c#视频教程

VIP服务:如果您的某个功能不会做,可以加我们QQ,给你做DEMO!

JQUERY  Asp.net教程

MVC视频教程  vs2012
.NET+sql开发
手机:15397600032 C#视频教程下载
微信小程序 vue.js高级实例视频教程

教程咨询QQ:1416759661


这篇文章不能解决你的问题?我们还有相关视频教程云课堂 全套前端开发工程师培训课程

微信号:yyjcw10000 QQ:1416759661  远程协助需要加QQ!

业务范围:视频教程|程序开发|在线解答|Demo制作|远程调试| 点击查看相关的视频教程

技术范围:全端开发/前端开发/webapp/web服务/接口开发/单片机/C#/java/node/sql server/mysql/mongodb/android/。 



关于我们 | 购买教程 | 网站建设 | 技术辅导 | 常见问题 | 联系我们 | 友情链接

夜鹰教程网 版权所有 www.yyjcw.com All rights reserved 备案号:蜀ICP备08011740号3