4、配置数据库审核规范

在本文中，我们假设对AdventureWorks数据库创建审核规范，DBA可以审核Production.Product表中的每一个选择，插入，更新和删除操作。

1、使用SQL Server Management Studio连接到SQL Server 2008实例。

2、在对象资源管理器中，右键点击“数据库à AdventureWorksà安全型à审核”节点，在弹出菜单中选择“新建数据库审核规范…”选项，创建一个新的数据库审核规范，如下图所示：

图7：新建数据库审核规范菜单项

3、SQL Server 2008将弹出一个新的窗口创建数据库审核，如图8所示：

图8：创建数据库审核规范弹出窗口

4、在操作列表中，“审核操作类型”字段我们选择SELECT;

在“对象类”字段我们选择Object;

点击“对象名称”字段旁的按钮，将弹出一个对话框来选择对象，在对话框中我们输入“[Production].[Product]”，如图9所示：

图9：选择对象

在图8界面中，点击“主体名称”字段旁的按钮，选择“PUBLIC”;

最后点击“确定”按钮。

5、按照第4个步骤，继续增加审核操作类型为“INSERT”、“UPDATE” 和 “DELETE”等的操作。增加完后，如图10所示。

最后点击“确定”按钮保存数据库审核规范。

图10：创建数据库审核规范

6、一旦审核对象创建后就必须启用。如图11所示：

图11：启用数据库审核规范菜单项

7. 如果数据库审核规范启用成功后，你将看到类似图12的提示消息框：

图12：成功启用数据库审核规范后的提示窗口

8. 在AdventureWorks 数据库中执行以下的T-SQL语句:

9、 右键点击“安全性à审核àSQLServerAuditing”节点，在弹出菜单中，选择“查看审核日志”来查看审核日志。

图13：查看审核日志菜单项

10、SQL Server将弹出一个对话框，显示SELECT事件的详细信息：

图14：查看审核日志信息

5、小结

SQL Server 2008中的数据库审核特性，允许数据库管理员，能够在SQL Server 2008上进行数据库级别上的审核。此特性是轻量级的，与现有的SQL跟踪， DDL触发器和事件通知等机制相比，开销也少。

原文：http://database.ctocio.com.cn/tips/218/8455718.shtml

作者: 林善茂, 　出处:IT专家网

【IT专家网独家】SQL Server 2008引入了SQL Server审核的 新特性。SQL Server数据库的审核涉及到数据库的跟踪和日志事件的记录。数据库管理员可以利用这个特性，来执行一项战略，审核 SQL Server 系统上的活动和更改，以满足特定风险的SQL Server 2008数据库的需求。

1、数据库审核规范

“数据库审核规范”对象也属于SQL Server审核。针对每个审核，DBA可以为每个 SQL Server 数据库创建一个数据库审核规范。

图1：SQL Server审核构成说明

数据库审核规范可收集由扩展事件功能引发的数据库级审核操作。您可以向数据库审核规范添加审核操作组或审核事件。“审核事件”是可以由 SQL Server 引擎审核的原子操作;“审核操作组”是预定义的操作组。它们都位于 SQL Server 数据库作用域。这些操作将发送到审核，审核将它们记录到目标中，，目标可以是文件、Windows 安全事件日志或 Windows 应用程序事件日志，如图1所示。管理员必须定期查看和归档这些日志，以确保目标具有足够的空间来写入更多记录。

属于db_owner角色的用户可以修改数据库中任何的审核规范。

2、如何使用SQL Server审核功能

DBA可以使用SQL Server Management Studio ( SSMS )或Transact-SQL来定义审核。默认情况下， SQL Server不会启用审核功能，因此我们创建好审核功能后，需要手动启用。

您可以使用Windows中的事件查看器阅读Windows安全事件日志或者Windows应用程序事件日志。您也可以使用日志文件查看工具在 SQL Server Management Studio或内置的FN_READ_AUDIT_FILE功能在SQL Server中读取目标文件。

创建和使用的审核功能的过程大体如下：

1、创建一个审核，并定义审核信息需要捕获的目标;

2、创建服务器审核规范或数据库审核规范;

3、启用审核规范。SQL Server创建审核时，默认是不启用审核规范的;

4、使用Windows事件查看器，查看日志文件或者使用内置FN_READ_AUDIT_FILE功能，分析所捕获的审核事件。

3、配置SQL Server 2008中的审核对象

必须先创建和配置可以用于审核的 SQL Server 审核对象，才可以创建数据库服务器审核规范。

1、使用SQL Server Management Studio连接到SQL Server 2008实例。

2、在对象资源管理器中，右键点击“安全性à审核”节点，在弹出菜单中选择“新建审核”选项，创建一个新的审核对象，如图2所示：

图2：新建审核菜单项

3、SQL Server 2008将弹出一个新的窗口创建审核，如图3所示：

图3：创建审核对象弹出窗口

您需要输入一些信息：

l 审核名称：提供审核对象的名称

l 队列延迟(毫秒)：指定在强制处理审核操作之前可以等待的时间(毫秒)。值 0 指示同步传递。默认的最小值为 1000(1 秒)。最大值为 2,147,483,647(2,147,483.647 秒，即24天20小时31分钟 23.647 秒)。

l 在审核日志故障时关闭服务器：在写入目标的服务器实例时，如果无法将数据写入审核目标时，强制关闭服务器。发出此命令的登录名必须具有 SHUTDOWN 权限。如果该登录名没有此权限，则该函数将失败并将引发错误消息。

最佳的做法是，只有当审核故障可能会威胁系统的安全性或完整性时，才应当使用此命令。

l 审核目标：指定数据的审核目标。可用选项包括二进制文件、Windows 应用程序事件日志或 Windows 安全事件日志。如果未在 Windows 中配置其他设置，SQL Server 无法写入 Windows 安全事件日志。需要注意的是，在 Windows XP 上不可写入安全事件日志。

l 文件路径：当指定“审核目标”是文件时，要将审核数据写入的该参数指定的文件夹所在的位置。

打开“浏览文件夹”对话框指定文件路径或者创建要写入审核文件的文件夹。

l 最大滚动更新文件数：指定要保留在文件系统中的最大审核文件数。设置为 MAX_ROLLOVER_FILES=UNLIMITED 时，未对将创建的滚动更新文件数施加任何限制。默认设置是无限的(UNLIMITED)。可以指定的最大文件数为 2,147,483,647。

l 最大文件大小：以兆字节 (MB) 为单位指定审核文件的最大大小。可以指定的最小大小为 1024 KB，最大大小为 2,147,483,647 TB。还可以指定 UNLIMITED(即不对文件大小施加限制)。指定一个小于 1024 KB 的值将引发错误 MSG_MAXSIZE_TOO_SMALL。默认设置为 UNLIMITED。

l 保留磁盘空间：指定在磁盘上预先分配与指定的最大文件大小相等的空间。只有当 MAXSIZE 不等于 UNLIMITED 时，才能使用此设置。默认设置为 OFF。

一旦审核对象创建后就必须启用。如图4所示：

图4：启用审核对象菜单项

5. 如果审核启用成功后，你将看到类似图5的提示消息框：

图5：成功启用审核后的提示窗口

6. 你可以右键点击SQLServerAuditing，在弹出菜单中，选择“View Audit Logs”来查看审核日志。

图6：查看审核日志菜单项

【IT专家网独家】

问题

当我们观察SQL Server 2008的新功能时，我们发现一个叫做数据压缩的可能很有趣的功能，我们可以用这个功能来缩小表，索引或者它们分区的一个子集。您能给我们提供如何利用这 个新功能的详细解释吗?

专家解答

在SQL Server中的数据压缩在SQL Server 2005 的Service Pack 2中已经可以使用了，在Service Pack 2中，引进了存储小数和数字数据。Vardecimal存储格式允许小数和数字数据类型以变长的形式存储。这个概念在SQL Server 2008中被扩展成全部定长的数据类型，比如integer，char和float 数据类型。数据压缩通过减少I/O和提高缓冲区命中率来降低储存成本和提高查询性能。

SQL Server 2008在表级别和索引级别上支持行和页的压缩。以下是两种类型的数据压缩的不同之处。

• 行压缩(ROW Compression) 这种压缩功能考虑到变长数据类型结构来定义一栏。比如，以变长存储的一个CHAR(100)栏只能使用由数据定义的存储空间大小。在栏中存储“SQL Server 2008”只要求存储15个字符而不是完整的100个字符，因此，在存储空间上节省率为85%。这是在SQL Server 2005 Service Pack 2中可以使用的 vardecimal存储格式的扩展。还要注意到，这个压缩功能没有因为零或者空值而占用磁盘空间。
• 页级别压缩(PAGE Compression) 这种压缩功能是行压缩的父集，它考虑到给定的页内的一行或多行冗余数据。它也使用前缀和字典压缩。这仅仅意味着对于两种页压缩技术，存储引擎在页内减少重 复数据。比如，如果利用一列前缀把一张表分区，在一个具体分区中的所有数据都会有一样或者相似的前缀。让我们以一些像A1000Q-xxxx的产品代码为 开始说一些栏的值，存储引擎存储A1000Q – 一开始在页上接着在相同的页面上从这个值的其他所有的发生的事指到这个值。这也可以说成有已经定义好的默认约束的一栏。页压缩只发生在页已经完全优化性能 的时候。

虽然表面看来数据压缩会缩小你的表和索引，但是最好首先通过

sp_estimate_data_compression_savings系统存储程序或数据压缩Wizard工具在表或索引中评估预计的节 省空间。你也可能检查现有数据是否成为碎片，因为你可能可以通过重建它而不是使用压缩来缩小索引。

评估存储节省空间

我们可以使用sp_estimate_data_compression_savings系统存储程序或者数据压缩Wizard工具去为索引中 的一张表找出预计的存储节省空间。为了使用AdventureWorks数据库中的

sp_estimate_data_compression_savings系统存储程序，

在这里，

l 第一个参数是模式名，

l 第二个参数是对象名，

l 第三个参数是索引id,

l 第四参数是分区id,

l 最后一个参数是压缩类型。

在以上的例子中，我会考虑在AdventureWorks数据库中的Sales.SalesOrderDetail表上的行压缩。执行这个查询 会给你如下显示的结果：

请注意栏size_with_current_compression_setting(KB)和

size_with_requested_compression_setting (KB)，假设没有碎片，这两个栏会让你对在Sales.SalesOrderDetail表中实现节省空间有个估计。

要使用数据压缩Wizard工具，右键单击Sales.SalesOrderDetail表，选择存储并且单击管理压缩。

这将在Welcome to Data Compression Wizard上启动数据压缩Wizard工具，点击下一步。

在选择压缩类型页面上，在压缩类型栏里点击下拉菜单，选择你的压缩类型选项。我将选择行，因为这是通过 sp_estimate_data_compression_savings系统存储程序使用的选项。点击计算按扭来显示由存储程序提供的相似信息。

这两种方法将会让你获得通过压缩你的表和索引来获得存储节省空间的一个估计。

使压缩在表上可用

为了使压缩在现有的非分区表上可用，可以使用有REBUILD选项的ALTER TABLE命令。

ALTER TABLE Sales.SalesOrderDetail

REBUILD WITH (DATA_COMPRESSION = ROW);

上面的语句使行压缩在Sales.SalesOrderDetail表上可用。你也可以继续用数据压缩Wizard去使压缩在表上可用。在点击 选择压缩类型页面上的计算按扭之后，点击下一步。在选择一个输出选项的页面上，你可以选择要么创建一个用于评论的脚本要么保存到一个文件中，立即运行或把 它当作一个工作。作为一个数据库管理员，你可能会把这个当作你的数据库日常维护的一部分，所以你可能也会为评论它而创建一个脚本，然后包括一个数据库维护 工作。点击下一步按扭来继续。

在点击完成之前，审查数据压缩摘要页面。

你可能想要创建一个脚本，这个脚本通过使用sp_estimate_data_compression_savings系统存储程序来遍历所有 的表，以此来产生摘要报告，而系统存储程序将会帮助你分析你会压缩哪些表或者索引。

原文：http://database.ctocio.com.cn/dbzjdysummary/285/8317285.shtml

6.MERGE 语句

在 SQL Server 2008 中，可以使用 MERGE 语句在一条语句中根据与源表联接的结果对目标表执行 INSERT、UPDATE 或 DELETE 操作。如：使用一个语句有条件地在单个目标表中插入或更新行，如果目标表中存在相应行，则更新一个或多个列;否则，会将数据插入新行。使用该语句还可以同 步两个表，根据与源数据的差别在目标表中插入、更新或删除行。

MERGE 语法包括如下五个主要子句：

MERGE 子句用于指定作为插入、更新或删除操作目标的表或视图。

USING 子句用于指定要与目标联接的数据源。

ON 子句用于指定决定目标与源的匹配位置的联接条件。

WHEN 子句用于根据 ON 子句的结果指定要执行的操作。

OUTPUT 子句针对更新、插入或删除的目标对象中的每一行返回一行。

其完整的语法格式如下：

使用下面的语句创建两个表：

  两个表中的数据如下图所示：

请注意，这两个表中有两个共有的产品-客户行：客户 11794 购买了产品 707，客户 15160 购买了产品 870。对于这些行，可以使用 WHEN MATCHED THEN 子句利用 Purchases 中这些购买记录的日期来更新 FactBuyingHabits。我们可以使用 WHEN NOT MATCHED THEN 子句将所有其他行插入 FactBuyingHabits。参考下面的语句：

$action用于在 OUTPUT 子句中指定一个 nvarchar(10) 类型的列，列的值是代表所执行操作的INSERT、UPDATE或DELETE。Inserted.*和Deleted.*分别用于指定返回所有插入行的 列和删除行的列。如果要指定具体的列，可以使用Inserted.ProductID这样的命名方式。

上面语句的输出结果如下：

再查询FactBuyingHabits表，可以看到被更新和插入后的结果，如下所示：

7.SQL 依赖关系报告

SQL Server 2008 引入了新的目录视图和系统函数用以提供一致可靠的 SQL 依赖关系报告。所谓依赖关系，通俗的讲：存储过程1需要使用存储过程2提供的结果，它们之间就是一种依赖关系。可以使用 sys.sql_expression_dependencies、sys.dm_sql_referencing_entities 和 sys.dm_sql_referenced_entities 来报告架构绑定和非架构绑定对象的跨服务器、跨数据库和数据库 SQL 依赖关系。

下例将创建一个表、一个视图和三个存储过程。这些对象将用在后面的查询中以演示如何报告依赖关系信息。可看到 MyView 和 MyProc3 均引用 Mytable。MyProc1 引用 MyView，而 MyProc2 引用 MyProc1。

下面的示例查询 sys.sql_expression_dependencies 目录视图以返回由 MyProc3 引用的实体。

下面是结果集：

上面的查询返回了两个在 MyProc3 定义中按名称引用的实体。服务器名称为 NULL，因为被引用实体没有使用有效的由四部分组成的名称指定。返回的结果中显示了 MyTable 的数据库名称，因为在存储过程中是使用由三部分组成的有效名称定义此实体的。

8.表值参数

数据库引擎引入了可以引用用户定义表类型的新参数类型。表值参数可以将多个数据行发送到 SQL Server 语句或例程(比如存储过程或函数)，而不用创建临时表。表值参数具有更高的灵活性，在某些情况下，可比临时表或其他传递参数列表的方法提供更好的性能。表 值参数具有以下优势：

首次从客户端填充数据时，不获取锁。

提供简单的编程模型。

允许在单个例程中包括复杂的业务逻辑。

减少到服务器的往返。

可以具有不同基数的表结构。

是强类型。

使客户端可以指定排序顺序和唯一键。

与其他参数一样，表值参数的作用域也是存储过程、函数或动态 Transact-SQL 文本。同样，表类型变量也与使用 DECLARE 语句创建的其他任何局部变量一样具有作用域。

与BULK INSERT操作相比，频繁使用表值参数将比大型数据集要快。大容量操作的启动开销比表值参数大，与之相比，表值参数在插入数目少于 1000 的行时具有很好的执行性能。

下面是SQL Server帮助中的示例，演示了如何执行以下操作：创建表值参数类型，声明变量来引用它，填充参数列表，然后将值传递到存储过程。

9.Transact-SQL 行构造函数

增强后的 Transact-SQL 可以允许将多个值插入单个 INSERT 语句中，语法比较简单。参考下面的代码：

      /* 创建一个表 */
CREATE TABLE dbo.T1(
CustName char(20) ,
ProductID int ,
MadeFrom char(20) ,
Sales numeric(20, 2)
)
/* 插入2行数据 */
INSERT INTO dbo.T1
VALUES ('Jane',1,'China',20.00),
('Jack',2,'USA',10.00)

原文：http://database.ctocio.com.cn/tips/457/8298457.shtml

Microsoft SQL Server 2008 对 Transact-SQL 语言进行了进一步增强，主要包括：ALTER DATABASE 兼容级别设置、复合运算符、CONVERT 函数、日期和时间功能、GROUPING SETS、MERGE 语句、SQL 依赖关系报告、表值参数和 Transact-SQL 行构造函数。

1.ALTER DATABASE 兼容级别设置

某些数据库行为与 SQL Server 版本有关，通过 ALTER DATABASE 下面新增的语法，可以设置数据库兼容级别，它取代了以前版本中的 sp_dbcmptlevel 过程。

可用的设置值80、90、100分别代表 SQL Server 2000、2005和2008。

2.复合运算符

SQL Server 2008 现在支持如下复合运算符，可执行操作并将变量设置为结果。

如：

3.CONVERT 函数

CONVERT 函数现在允许在二进制和字符十六进制值之间进行转换。函数语法格式如下：

expression 是被转换的有效的表达式，data_type 目标数据类型(不能使用别名数据类型)，length 指定目标数据类型长度的可选整数，style 指定 CONVERT 函数如何转换 expression 的整数表达式。

如果 expression 为 binary(n)、varbinary(n)、char(n) 或 varchar(n)，则 style 可以为下表中显示的值之一。

参考下面的示例代码：

--转换二进制值 0x4E616d65 到一个字符值

--下面的示例演示了 Style 为 1 的情况下,如何强行截断结果值。

--产生的结果值由于包含字符 0x ，所以被截断

--下面的示例演示了 Style 为 2 的情况下，没有截断结果值。

--这是因为 0x 字符未包含在结果中

对于 style 1，将在转换后的结果左侧添加字符 0x。作为要转换的二进制表达式，字符 0x 必须为表达式中的前两个字符。

在style为2的情况下，生成的二进制值不会包含字符 0x。作为要转换的二进制表达式，也不需要在字符前面包含字符 0x。

如果 data_type 为二进制类型，则表达式必须为字符表达式。

如果转换后的表达式长度大于 data_type 长度，则会在右侧截断结果。

如果固定长度 data_types 大于转换后的结果，则会在结果右侧添加零。

如果 data_type 为字符类型，则表达式必须为二进制表达式。每个二进制字符均转换为两个十六进制字符。如果转换后的表达式长度大于 data_type 长度，则会在右侧截断结果。

如果 data_type 为固定大小的字符类型，并且转换后的结果长度小于其 data_type 长度，则会在转换后的表达式右侧添加空格，以使十六进制数字的个数保持为偶数。

--转换字符值 Name 到一个二进制值

结果如下：

4.日期和时间功能

DATEPART ( datepart , date )函数用于返回 date中的指定 datepart 的整数。如：

SELECT DATEPART(YEAR,'2007-05-10') --返回2007

SQL Server 2008 包含对 ISO 周-日期系统的支持，即周的编号系统。每周都与该周内星期四所在的年份关联。例如，2004 年第 1 周 (2004W01) 从 2003 年 12 月 29 日星期一到 2004 年 1 月 4 日星期天。一年中最大的周数可能为 52 或 53。这种编号方式通常用于欧洲国家，但其他国家/地区很少用到。

下面分别是2010年和2009年1月份的日历。由于2010年第一个星期中的星期四是2010-1-7日，所以2010-1-3日及之前的日 期会作为2009年的第53个星期，而不是2010年的第一个星期。而对于2009年1月份的日历，由于星期四是2009-1-1，所以该星期会作为 2009年的第一个星期。当然，该星期也包含了2008-12-28至31的4天。

参考下面的代码：

5.ROLLUP、CUBE 和GROUPING SETS

在SQL Server 2008之前，进行分组统计汇总，可以在GROUP BY子句中使用WITH ROLLUP和WITH CUBE参数。ROLLUP指定在结果集内不仅包含由GROUP BY提供的行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。而CUBE参数则在使用ROLLUP参数所返回结果集的基础上，再将每 个可能的组和子组组合在结果集内返回。

例如，假设dbo.T1表中存在下列数据：

执行下面的查询语句：

得到下面的结果集合，可以看出，使用WITH CUBE多出了对子组ProductID的两行汇总。

而在SQL Server 2008中，GROUPING SETS、ROLLUP 和 CUBE 运算符已添加到 GROUP BY 子句中。不再推荐使用不符合 ISO 的 WITH ROLLUP、WITH CUBE 和 ALL 语法。在SQL Server 2008中，可以将上面的WITH CUBE语句改写为如下的形式：

如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组，则可以使用 GROUPING SETS 仅指定所需的分组。例如，下面的语句将得到分别按CustName和ProductID分组汇总结果集的并集。

结果集如下：

上面的语句等同于下面的UNION ALL语句：

      SELECT CustName,NULL AS ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName
UNION ALL
SELECT NULL AS CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY ProductID

原文：http://database.ctocio.com.cn/tips/363/8298363.shtml

1.简介

EXCEPT和INTERSECT运算符使您可以比较两个或多个SELECT语句的结果并返回非重复值。

2.区别

EXCEPT运算符返回由EXCEPT运算符左侧的查询返回、而又不包含在右侧查询所返回的值中的所有非重复值。

INTERSECT返回由INTERSECT运算符左侧和右侧的查询都返回的所有非重复值。

3.注意事项

(1).使用EXCEPT或INTERSECT比较的结果集必须具有相同的结构。它们的列数必须相同，并且相应的结果集列的数据类型必须兼容

(2).INTERSECT运算符优先于EXCEPT

(3).SELECT INTO必须是包含INTERSECT或EXCEPT运算符的语句中的第一个查询，用来创建容纳最终结果集的表

(4).ORDER BY子句中的列名或别名必须引用左侧查询返回的列名

4.例题：

--建立3个表，分别插入数据

--利用EXCEPT

--找出TableA表的col1列不存在Tablec表col1列的所有非重复值

结果如下：

col1

-----------

NULL

2

3

4

--sql 2000的版本，用not exists实现EXCEPT的功能

--sql 2000,not in是得不到上述结果的

--空值表示值未知。空值不同于空白或零值。没有两个相等的空值。

--比较两个空值或将空值与任何其他值相比均返回未知，这是因为每个空值均为未知。

--使用IN或NOT IN比较后返回的所有空值都将返回UNKNOWN。

--将空值与IN或NOT IN一起使用会产生意外结果。

结果如下：

col1

-----------

2

3

4

--INTERSECT运算符优先于EXCEPT

--运算步骤是：先运算TableB和TableC的INTERSECT,再和TableA运算EXCEPT

  　　SELECT col1 FROM TableA
EXCEPT
SELECT col2 FROM TableB
INTERSECT
SELECT col3 FROM TableC

结果如下：

col1

-----------

NULL

2

3

4

5

--SELECT INTO的应用

--SELECT INTO必须是语句中的第一个查询

--我记得SELECT INTO与UNION运算符的使用也是这样的规则

结果如下：

col1

-----------

NULL

2

3

4

--ORDER BY子句

--ORDER BY子句中的列名或别名必须引用左侧查询返回的列名

结果如下：

col1

-----------

1

5

原文：http://database.ctocio.com.cn/tips/456/8275956.shtml