adventureworks2008(什么是adventureworks2008)

编程之家2024-05-1284次浏览

一、sql server 2008中怎么自定义列排序呢

一、使用排序规则

adventureworks2008(什么是adventureworks2008)

排序规则指定字符串数据如何比较和排序的规则，基于特定的语言与区域标准。例如，在ORDER BY子句中，如果按升序排列的话，说英语的人会期望字符串'Chiapas'出现在'Colima'之前；然而，说西班牙语的墨西哥人将期望以‘Ch’开头的单词出现在以'C'开头的单词列表的末尾。排序规则负责控制这些类型的比较与排序规则。在 ORDER BY ASC的子句中，Latin_1 General排序规则将‘Chiapas’排在‘Colima’之前，而Traditional_Spanish排序规则将‘Chiapas’排在‘Colima’之后。

当为非Unicode字符数据如char,varchar,text指定排序规则时，一个特定的code page将与之关联。例如，如果数据表中为char类型的某列定义了Latin1_General排序规则，那么，SQL Server将使用1252 code page解释和显示该列中的数据。

对于非Unicode数据，多个排序规则可以使用相同的code page。而对于纯Unicode数据如nchar,nvarchar,nvachar(max),指定的排序规则则没有与之关联的code page。Unicode数据能够处理大多数的通用字符。

二、设置和改变排序规则

排序规则可以在分别在server,database,column,expression和identifier层级指定。当你安装SQL Server实例时，你为该实例指定默认的server级排序规则。每次你创建数据库，你可以为该数据库指定默认的排序规则。如果你没有指定排序规则，那么服务器实例的默认排序规则将作为该数据库的默认排序规则。无论何时你定义字符类型的列、变量或参数，都可以为该对象指定排序规则。若你没有指定，将使用数据库的默认排序规则创建该对象。

三、应用范例

查看当前SQL Server Instance的排序规则设置

select serverproperty('collation') as CollationSetting

//Chinese_PRC_CI_AS

查看SQL Server Instance支持的排序规则列表

SELECT* FROM::fn_helpcollations()

查看用户数据库的排序规则设置

select databaseproperty('jiradb','collation') as CollationSetting

// NULL(未指定，将应用server实例的设置)

select DATABASEPROPERTYEX('jiradb','collation') as CollationSetting

//Chinese_PRC_CI_AS

SELECT name, collation_name FROM sys.databases WHERE name='jiradb'

//Chinese_PRC_CI_AS

修改数据库的排序规则设置

ALTER DATABASE jiradb COLLATE Latin1_General_CS_AS

修改Column的排序规则设置

CREATE TABLE MyTable

(PrimaryKey int PRIMARY KEY,

CharCol varchar(10) COLLATE French_CI_AS NOT NULL

)

ALTER TABLE MyTable ALTER COLUMN CharCol

varchar(10)COLLATE Latin1_General_CI_AS NOT NULL

在查询的ORDER BY子句中指定排序规则，覆盖server、database或column层级的默认排序规则

USE AdventureWorks2008R2;

SELECT LastName FROM Person.Person

ORDER BY LastName

COLLATE Traditional_Spanish_ci_ai ASC;

忽略重音符号

select*

from dbo.Restaurants

where Name collate SQL_Latin1_General_CP1_CI_AI like'Cafe'

//返回结果集中将包含'Cafe'和'Café'。

二、SQLServer2008基础教程的目录

第1章SQLServer2008概述和安装

1.1为什么选择SQLServer2008

1.2SQLServer的发展

1.3硬件要求

1.3.1CPU

1.3.2内存

1.3.3硬盘空间

1.3.4操作系统要求

1.4示例

1.5安装

1.5.1开始安装

1.5.2选择要安装的功能

1.5.3为实例命名

1.5.4选择服务账户

1.5.5选择身份验证模式

1.5.6确定数据目录的位置

1.5.7创建报表服务数据库

1.5.8配置错误和使用情况报告

1.6安全性

1.6.1服务账户

1.6.2身份验证模式

1.6.3sa登录

1.7小结

第2章SSMS

2.1SSMS概览

2.2SSMS的选项

2.2.1“环境”节点

2.2.2“源代码管理”节点

2.2.3“文本编辑器”节点

2.2.4“查询执行”节点

2.2.5“查询结果”节点

2.3查询编辑器

2.4小结

第3章设计和创建数据库

3.1数据库的定义

3.2SQLServer中的预建数据库

3.2.1master.

3.2.2tempdb

3.2.3mode1

3.2.4mSdb

3.2.5AdventureWorkS/Adventure-WorksDW

3.3选择数据库系统类型

3.3.1OLTP

3.3.2OLAP

3.3.3示例系统类型选择

3.4收集数据

3.5确定要存储在数据库中的信息

3.5.1金融产品

3.5.2客户

3.5.3客户地址

3.5.4股票

3.5.5交易

3.6外部信息和忽略的信息

3.7建立关系

3.7.1使用键

3.7.2创建关系

3.7.3关于外键的更多信息

3.8规范化

3.8.1每一个实体都应该有唯一的标识符

3.8.2只存储与实体直接有关的信息

3.8.3避免重复值或重复列

3.8.4范式

3.8.5非规范化

3.9创建示例数据库

3.9.1在SSMS中创建数据库

3.9.2在SSMS中删除数据库

3.9.3在查询编辑器中创建数据库一

3.10小结

第4章安全和遵从规范

4.1登录名

4.2服务器登录名和数据库用户

4.3角色

4.3.1固定服务器角色

4.3.2数据库角色

4.3.3应用程序角色

4.4架构

4.5在解决方案中进行下去之前

4.6陈述式管理框架

4.7小结

第5章定义表

5.1什么是表

5.2SQLServer数据类型

5.2.1表中的数据类型

5.2.2程序中的数据类型

5.3列不仅仅是简单的数据存储库

5.3.1默认值

5.3.2生成IDENTIIY值

5.3.3NULL值的使用

5‘3.4为什么要定义允许NULL值的列

5.4在SQLServer中存储图像和大型文本

5.5在SSMSq户创建表

5.6通过查询编辑器创建表

5.7创建表：使用模板

5.8创建模板和修改模板

5.9ALTERTABLE语句

5.10定义其余的表

5.11设置主键

5.12创建关系

5.12.1在创建时检查现有数据

5.12.2强制外键约束

5.12.3选择删除/更新规则

5.13通过T-SQL语句建立关系

5.14小结

第6章创建索引和数据库关系图

6.1什么是索引

6.1.1索引类型

6.1.2唯一性

6.2确定是什么创建了好的索引

6.2.1使用低维护列

6.2.2主键和外键

6.2.3找到指定记录

6.2.4使用覆盖索引

6.2.5查找信息范围

6.2.6保持数据的排序

6.3确定是什么导致了坏的索引

6.3.1使用了不合适的列

6.3.2选择了不合适的数据

6.3.3包含了过多的列

6.3.4表中包含的记录过少

6.4针对性能对索引进行审查

6.5创建索引

6.5.1用表设计器创建索引

6.5.2索引和统计信息

6.5.3CREATEINDEX语法

6.5.4在查询编辑器中创建索引：模板

6.5.5在查询编辑器中创建索引：SQL代码

6.6删除索引

6.7在查询编辑器中修改索引

6.8当索引不存在时

6.9为数据库生成关系图

6.9.1数据库关系图基础

6.9.2SQLServer数据库关系图工具

6.9.3默认的数据库关系图

6.9.4数据库关系图工具条

6.10小结

第7章数据库的备份、还原和维护

7.1事务日志

7.2备份策略

7.3当可能发生错误时

7.4让数据库脱机

7.5备份数据

7.5.1使用T-SQL备份数据库

7.5.2使用T.SQL进行日志备份

7.6还原数据库

7.6.1使用SSMS还原数据

7.6.2使用T－SQL进行还原

7.7分离和附加数据库

7.7.1使用SSMS进行分离和附加操作

7.7.2使用T.SQL进行分离和附加操作

7.8为数据库生成SQL脚本

7.9维护数据库

7.10创建数据库维护计划

7.11设置数据库邮件

7.12修改维护计划

7.13小结

第8章操作数据

8.1T－SQL的INSERT命令的语法

8.2SQL命令INSERT

8.2.1默认值

8.2.2使用NULL值

8.3DBCCCHECKIDENT

8.4列约束

8.5同时插入多条记录

8.6检索数据

8.7使用SSMS检索数据

8.8SELECT语句

8.9指定列

8.10第一批搜索

8.1l改变输出的显示

8.1.2限制搜索：NLIERE的使用

8.12.1SETROWCOUNTn

8.12.2TOPn

8.12.3TOPnPERCENT

8.13字符串函数

8.14顺序！顺序！

8.15LIKE运算符

8.16生成数据：SEL.ECTINT

8.17谁能添加、删除或选取数据

8.18更新数据

8.18.1JJPDAT命令

8.18.2在查询编辑器中更新数据

8.19事务

8.19.1BEGINTRAN

8.19.2COMMI：TTRAN

8.19.3ROLLBACKTRAN

8.19.4锁定数据

8.19.5更新数据：使用事务

8.19.6嵌套事务

8.20删除数据

8.20.1DFLETE的语法

8.20.2使用DELETE语句

8.21截断表（删除表中的所有行）

8.22删除表

8.23小结

第9章构建视图

9.1为什么要构建视图

9.2针对安全而使用查询

9.3加密视图定义

9.4创建视图：SSMS、

9.5使用视图来创建视图

9.6CREATEVIEW语法

9.7创建视图：查询编辑器窗格

9.8创建视图：SCFIEMABINDING

9.9为视图设置索引

9.10小结.

第10章存储过程和函数

10.1什么是存储过程.

10.2CREATPROCEDURE语法

10.3返回一系列记录

10.4创建存储过程：SSMS

10.5执行存储过程的不同方法

10.5.1不使用EXEC

10.5.2使用EXEC

10.6使用RETURN

10.7控制流程

10.7.1IFELSE

10.7.2BEGINEND

10.7.3NHILEBREAK语句

10.7.4CASF吾句

10.8综合应用

10.9用户定义函数.

10.9.1标量函数

10.9.2表值函数

10.9，3创建用户定义函数时要考虑的事项

10.10小结

第11章T-SQL基础

11.1使用多个表

11.2变量

11.3临时表

11.4聚合

11.4.1COUNT/COUNT_BIG

11.4.2SUH

11.4.3MAX/MIN

11.4.4AVG

11.5分组数据

11.6HAVINC

11.7独特值

11.8函数

11.8.1日期和时间函数

11.8.2字符串函数

11.8.3系统函数

11.9RAISTRROR

11.10错误处理

11.11@@ERROR

11.12TRYCATCH

11.13小结

第12章高级T-SQL

12.1子查询

12.1.1IN

12.1.2ExISTs

12.1.3了结未了之事

12.2APPLY运算符

12.2.1CROssAPPLY

12.2.2OUTERAPPI-Y

12.3公用表表达式

12.4透视数据

12.4.1PIVOT

12.4.2LINPIVOT

12.5排名函数

12.5.1ROWNUMBER

12.5.2RANK

12.5.3DENSERANK

12.5.4NTILE

12.6SQLServer中的PowerShell

12.7小结

第13章触发器

13.1什么是触发器

13.2DML触发器.

13.3针对DML触发器的CREATETRIGGFR语法

13.4为什么不使用约束

13.5对逻辑表进行删除和插入

13.6创建DMLFOR触发器

13.7检查特定的列

13.7.1使用UPDATE（）

13.7.2使用COLUJMNS_JPDATED

13.8DDL触发器

13.8.1DDL_OATABAS_LFVELEVENT5

13.8.2删除DDL触发器

13.8.3EVENTDAT（）

13.9小结

第14章SQLServer2008ReponingServices

14.1ReportingServices的架构

14.2配置ReportingServices

14.3使用报表设计器构建第一个报表

14.4.小结

三、SQLServer 2008 CDC实现数据变更捕获使用图文详解

适用环境：

仅在SQLServer2008（含）以后的企业版、开发版和评估版中可用。

详解：

CDC功能主要捕获SQLServer指定表的增删改操作，由于任何操作都会写日志（哪怕truncate），所以CDC的捕获来源于日志文件。日志文件会把更改应用到数据文件中，同时也会标记符合要求的数据标记为需要添加跟踪的项。然后通过一些配套函数，最后写入到数据仓库中。大概流程：

步骤：本文中以：AdventureWorks为例

第一步、对目标库显式启用CDC：

在当前库使用sys.sp_cdc_enable_db。返回0（成功）或1（失败）。注意，无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。

该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。

使用以下代码启用：

复制代码

代码如下:

/ppUSE AdventureWorks/ppGO/ppEXECUTE sys.sp_cdc_enable_db;

GO/pp

在一开始直接执行时，出现了报错信息：

消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第193行

无法更新元数据来指示已对数据库AdventureWorks启用了变更数据捕获。执行命令'SetCDCTracked(Value= 1)'时失败。返回的错误为15517:'无法作为数据库主体执行，因为主体"dbo"不存在、无法模拟这种类型的主体，或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。

这里引出了另外一个知识点：错误号 15517的错误

这种错误会在很多地方出现，如还原数据库的时候也会有可能出现。共同点是：某个/些存储过程使用了具有WITHEXECUTE AS的选项。使其在当前库具有了某个架构，但是当在别的地方执行时，由于没有这个架构，所以就报错，解决方法：

ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa]

经过检查，uspUpdateEmployeeHireInfo这个存储过程的确有：WITH EXECUTE AS CALLER

使用sa的原因是即使sa被禁用，sa还是存在的。所以不会报错。

现在重新执行：

复制代码

代码如下:

/ppUSE AdventureWorks/ppGO/ppEXECUTE sys.sp_cdc_enable_db;/ppGO/pp

启用成功，然后通过以下语句检查是否成功：

复制代码

代码如下:

/ppSELECT is_cdc_enabled,CASEWHENis_cdc_enabled=0THEN'CDC功能禁用'ELSE'CDC功能启用'END描述/ppFROM sys.databases/ppWHERE NAME='AdventureWorks'

创建成功后，将自动添加CDC用户和CDC架构。

创建这两个用户、架构的原因是因为CDC要求独占方式使用这两个架构，所以要单独创建。如果存在了非CDC功能创建的CDC用户、架构的话，则需要先删除该cdc命名的架构，才能开启。

第二步、对目标表启用CDC：

使用db_owner角色的成员执行sys.sp_cdc_enable_table为每个需要跟踪的表创建捕获实例。然后通过sys.tables目录视图中的is_tracked_by_cdc列来判断是否创建成功。

默认情况下会对表的全部列做捕获。如果只需要对某些列做捕获，可以使用@captured_column_list参数指定这些列。

如果要把更改表放到文件组里的话，最好创建单独的文件组（最起码与源表独立）。

如果不想控制访问角色，则@role_name必须显式设置为null。

复制代码

代码如下:

/ppsys.sp_cdc_enable_table/pp [@source_schema= ]'source_schema',/pp [@source_name= ]'source_name',/pp [@role_name= ]'role_name'/pp [,[@capture_instance= ]'capture_instance' ]/pp [,[@supports_net_changes= ] supports_net_changes ]/pp [,[@index_name= ]'index_name' ]/pp [,[@captured_column_list= ]'captured_column_list' ]/pp [,[@filegroup_name= ]'filegroup_name' ]/pp [,[@partition_switch= ]'partition_switch' ]/pp

例子：

把HumanResources.Department这个表开启变更捕获。

复制代码

代码如下:

/ppUSE AdventureWorks/ppGO/ppEXEC a href="mailto:sys.sp_cdc_enable_table@source_schema='HumanResources'"sys.sp_cdc_enable_table@source_schema='HumanResources'/a,/pp@source_name='Department',@role_name= NULL/pp

然后查询是否成功：

对表开启以后，可以在下图中看到多了很多cdc架构开头的表：

启动之后，可以看到SQLServer代理里面的作业，也出现了这两个作业：

案例：

下面开始从头到尾做一个实际案例：

先检查原库的内容：可以看到系统表里面只有一个dbo.sysdiagrams表

然后看看SQLServer代理：可以看到也没有相关的作业

步骤二：对数据库启用CDC数据库级别功能。必须显式启用：

Step1、执行以下语句：

复制代码

代码如下:

/pp align="left"USE AdventureWorks/pp align="left"GO/pp align="left"EXEC sys.sp_cdc_enable_db/ppGO/pp

某些数据库可能存在一些存储过程包含有：execute as等语句，此时会报错：

文字

消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第186行

消息266，级别16，状态2，过程sp_cdc_enable_db_internal，第0行

EXECUTE后的事务计数指示BEGIN和COMMIT语句的数目不匹配。上一计数= 0，当前计数= 1。

消息266，级别16，状态2，过程sp_cdc_enable_db，第0行

EXECUTE后的事务计数指示BEGIN和COMMIT语句的数目不匹配。上一计数= 0，当前计数= 1。

消息3998，级别16，状态1，第1行

在批处理结束时检测到不可提交的事务。该事务将回滚。

如果出现这个错误，目前的解决方法是执行下面语句，原因已在开头说明，对于没有使用EXECUTE AS的库，一般不会有这样的问题：

ALTER AUTHORIZATIONON DATABASE::[AdventureWorks]TO [sa]

然后再次执行，就成功开启了：

复制代码

代码如下:

/pp align="left"USE AdventureWorks/pp align="left"GO/pp align="left"EXEC sys.sp_cdc_enable_db/ppGO/pp

现在检查是否成功：

复制代码

代码如下:

/pp align="left"SELECT is_cdc_enabled,/pp align="left" CASE WHEN is_cdc_enabled= 0 THEN'CDC功能禁用'/pp align="left" ELSE'CDC功能启用'/pp align="left" END描述/pp align="left"FROM sys.databases/pp align="left"WHERE NAME='AdventureWorks'/pp

/pp

现在检查表和作业：

作业没有改变

角色权限中多出了：

步骤三：对某些表开启捕获：

这里选择HumanResources.Department、Person.ADDRESS、Person.Contact开启：

先来看开启之前的样子：

然后对该表开启：

复制代码

代码如下:

/pp align="left"USE AdventureWorks;/pp align="left"GO/pp align="left"EXECUTE sys.sp_cdc_enable_table/pp align="left"@source_schema= N'HumanResources'/pp align="left",@source_name= N'Department'/pp align="left",@role_name= N'cdc_Admin'--可以自动创建/pp align="left",@capture_instance=DEFAULT/ppGO/pp

结果如图:

可以看到在创建的同时,也创建了两个作业: cdc.AdventureWorks_cleanup和cdc.AdventureWorks_capture

也可以看到多了一个角色CDC_ADMIN，是在上面语句中动态创建的：

按照上面步骤把另外两个表也开启了：

复制代码

代码如下:

/divdivp align="left"USE AdventureWorks;/pp align="left"GO/pp align="left"EXECUTE sys.sp_cdc_enable_table/pp align="left"@source_schema= N'Person'/pp align="left",@source_name= N'ADDRESS'/pp align="left",@role_name= N'cdc_Admin'--可以自动创建/pp align="left",@capture_instance=DEFAULT/pp align="left"GO/pp align="left"EXECUTE sys.sp_cdc_enable_table/pp align="left"@source_schema= N'Person'/pp align="left",@source_name= N'Contact'/pp align="left",@role_name= N'cdc_Admin'--可以自动创建/pp align="left",@capture_instance=DEFAULT/pp align="left"GO/pp

可以从系统表中看到：

了3个表，并且是刚才开启CDC功能的表。现在来检查是否开启成功：

复制代码

代码如下:

/divp align="left"SELECT name,/pp align="left" is_tracked_by_cdc,/pp align="left" CASE WHEN is_tracked_by_cdc= 0 THEN'CDC功能禁用'/pp align="left" ELSE'CDC功能启用'/pp align="left" END描述/pp align="left"FROM sys.tables/pp align="left"WHERE OBJECT_ID IN(OBJECT_ID('HumanResources.Department'),/pp align="left" OBJECT_ID('Person.ADDRESS'),/pp align="left" OBJECT_ID('Person.Contact'))/pp

结果如下：

步骤三：检验：

下面来改动数据：

然后把表中的数据复制一份：

复制代码

代码如下:

/pp align="left"INSERT INTO HumanResources.Department/pp align="left"(Name,/pp align="left" GroupName,/pp align="left" ModifiedDate/pp align="left")/pp align="left" SELECT Name+'1',/pp align="left" GroupName+'1',/pp align="left" GETDATE()ModifiedDate/pp FROM HumanResources.Department/pp

结果如下：

然后从cdc.HumanResources_Department_CT表查询：

可以看到的确多了16条记录。这部分记录证明了，有16条数据从监控到目前为止做了改动，现在再来改动一下：

DELETE FROM HumanResources.DepartmentWHERE DepartmentID17

再查询cdc表，可以看到又多了16条记录：

现在来做下update的实验：

UPDATE HumanResources.DepartmentSET ModifiedDate=GETDATE()

再查看：

现在来分析一下这个表：

可以在联机丛书上查看：cdc.capture_instance_CT 可以看到，这样命名的表，是用于记录源表更改的表。对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。

对于__$operation列：1=删除、2=插入、3=更新（旧值）、4=更新（新值）

对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）

但是微软不检查直接查询这类表，建议使用cdc.fn_cdc_get_all_changes_捕获实例和cdc.fn_cdc_get_net_changes_capture_instance来查询

下文开始，来熟悉各种函数、存储过程的使用，并尝试一些不正常的操作。

日常使用情景：1、查询已经开启的捕获实例：

由于可能不记得或者不知道开启了什么表的捕获，所以可以使用以下语句来查找：

--返回所有表的变更捕获配置信息

复制代码

代码如下:

/pp align="left"EXECUTE sys.sp_cdc_help_change_data_capture;/ppGO/pp align="left"

可以看到以下截图：

查看对某个实例(即表)的哪些列做了捕获监控:

复制代码

代码如下:

/divdivp align="left"EXEC sys.sp_cdc_get_captured_columns/pp@capture_instance='HumanResources_Department'-- sysname/pp

得到下图:

也可以从下面中查找配置信息：

SELECT* FROM msdb.dbo.cdc_jobs

如图：

2、查看当前配置使用sp_cdc_help_jobs：

从上文可以看到，启用cdc之后会自动创建了两个作业，可以先使用以下语句来查看：

sp_cdc_help_jobs

得到的结果：

对于一个大型的OLTP系统，由于数据更改会非常频繁，变更表中的数据会非常多，如果存放过久（最久可以存放100年），那对数据库空间是非常大的挑战。此时可以调整上图中cdc.AdventureWorks_cleanup中retention（单位：分钟）。

3、修改配置：sp_cdc_change_job：

--显示原有配置：

复制代码

代码如下:

/pp align="left"EXEC sp_cdc_help_jobs/pp align="left"GO/pp align="left"--更改数据保留时间为分钟

EXECUTE sys.sp_cdc_change_job/pp align="left"@job_type= N'cleanup',/pp align="left"@retention=100/pp align="left"GO/pp align="left"--停用作业/pp align="left"EXEC sys.sp_cdc_stop_jobN'cleanup'/pp align="left"GO/pp align="left"--启用作业/pp align="left"EXEC sys.sp_cdc_start_jobN'cleanup'/pp align="left"GO/pp align="left"--再次查看/pp align="left"EXEC sp_cdc_help_jobs/ppGO/pp align="left"

得到以下结果：

证明修改成功，此处注意，修改后要先停用（如果已经启用），再启用，才能生效。

4、停止/启用、删除/创建作业：

停止/开始作业，可以使用以下语句：

复制代码

代码如下:

/pp align="left"--停用作业/pp align="left"EXEC sys.sp_cdc_stop_jobN'cleanup'/pp align="left"GO/pp align="left"--启用作业/pp align="left"EXEC sys.sp_cdc_start_jobN'cleanup'/pp align="left"GO/pp

删除作业：

复制代码

代码如下:

/pp align="left"EXEC sys.sp_cdc_drop_job@job_type= N'cleanup'-- nvarchar(20)/pp align="left"GO/pp align="left"--查看作业/pp align="left"EXEC sys.sp_cdc_help_jobs/ppGO/pp

可以看到现在只剩下一个作业了:

创建作业：

复制代码

代码如下:

/pp align="left"EXEC sys.sp_cdc_add_job/pp align="left"@job_type= N'cleanup',/pp align="left"@start_job= 0,/pp align="left"@retention= 5760/pp align="left"--查看作业/pp align="left"EXEC sys.sp_cdc_help_jobs/ppGO/pp

下面看到已经创建成功:

5、DDL变更捕获：

CDC除了捕获数据变更之外，还能捕获DDL操作的变化。前提是先要确保SQLServer代理的启用，其实CDC功能都需要确保sql代理正常运行，因为所有操作都通过代理中的两个作业来实现的。

现在先来对HumanResources.Department表修改一下，把name的长度加长：

复制代码

代码如下:

/pp align="left"ALTER TABLEHumanResources.DepartmentALTER COLUMN Name NVARCHAR(120);/ppGO/pp

然后查询ddl记录表：

复制代码

代码如下:

/pp align="left"SELECT*/ppFROM cdc.ddl_history/pp

可以看到：

由于在截图之前已经试了几次，所以里面有3条数据，之所以试了几次，就是因为上面所说的，忘了开SQL代理，所以查不出数据，所以切记要开启SQL代理。

6、使用CDC的函数来获取更改：

A、使用cdc.fn_cdc_get_all_changes_HumanResources_Department函数报告捕获实例HumanResources_Department的当前所有可用更改：

复制代码

代码如下:

/pp align="left"DECLARE@from_lsn binary(10),@to_lsn binary(10)/pp align="left"SET@from_lsn=/pp align="left" sys.fn_cdc_get_min_lsn('HumanResources_Department')/pp align="left"SET@to_lsn= sys.fn_cdc_get_max_lsn()/pp align="left"SELECT*FROM cdc.fn_cdc_get_all_changes_HumanResources_Department/pp align="left"(@from_lsn,@to_lsn, N'all update old');/pp align="left"GO/pp

B、获取某个时间段的更改信息：

先根据日志序列号（logsequence number,LSN）来获取跟踪变更数据：

Sys.fn_cdc_map_time_to_lsn获取变更范围内的最大、最小LSN值。可以使用：

Smallest greater than;smallest greater than orequal;largest less than;largest less than or equal.

如查询某个时间段插入的数据：

--插入数据

复制代码

代码如下:

/pp align="left"INSERT INTOHumanResources.Department(name,GroupName,ModifiedDate)/pp align="left"VALUES('test','abc',GETDATE())/pp align="left"INSERT INTOHumanResources.Department(name,GroupName,ModifiedDate)/pp align="left"VALUES('test1','abc1',GETDATE())/pp align="left"go/pp align="left"

--检查数据

复制代码

代码如下:

/pp align="left"DECLARE@bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal','2012-10-12 12:00:00.997')/pp align="left"DECLARE@edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE())/pp align="left"SELECT DepartmentID,GroupName,Name/pp align="left"FROM cdc.HumanResources_Department_CT/ppWHERE [__$operation]=2AND [__$start_lsn]BETWEEN@bglsn AND@edlsn/pp align="left"

得到以下结果：

C、sys.fn_cdc_map_lsn_to_time查询变更时间：

复制代码

代码如下:

/pp align="left"SELECT [__$operation],/pp align="left" CASE [__$operation] WHEN 1 THEN'删除'WHEN 2 THEN'插入' WHEN 3 THEN'更新（捕获的列值是执行更新操作前的值）'/pp align="left" WHEN 4 THEN'更新（捕获的列值是执行更新操作后的值）'END [类型],/pp align="left" sys.fn_cdc_map_lsn_to_time([__$start_lsn])[更改时间],/pp align="left" name,/pp align="left" DepartmentID,/pp align="left" GroupName,/pp align="left" ModifiedDate/pp align="left"FROM cdc.HumanResources_Department_CT/pp

结果:

注意,由于该表刚好有一个modfieddate字段,所以和更改时间相同.

D、获取LSN边界：

复制代码

代码如下:

SELECT sys.fn_cdc_get_max_lsn()[数据库级别的最大LSN],

sys.fn_cdc_get_min_lsn('cdc.HumanResources_Department_CT')[捕获实例的lsn]

结果如下：

这两个值可以用于上面提到的函数里面用于筛选数据之用。

xp挑战赛战况(谁能成为最终的XP挑战赛冠军)sumifs函数的使用方法？sumifs多条件求和步骤