SQLServer2005Beta2Transact-SQL功能（1）

核心提示：简介和范围该白皮书介绍了 Microsoft sql server(WINDOWS平台上强大的数据库平台) 2005 Beta 2 中的 Transact-SQL 的几个新的增强功能，这些新功能可以改善您的表达能力、查询性能以及错误管理功能，SQLServer2005Beta2Transact-SQL功能（1），本文重

　　简介和范围
　　
　　该白皮书介绍了 Microsoft sql server(WINDOWS平台上强大的数据库平台) 2005 Beta 2 中的 Transact-SQL 的几个新的增强功能。这些新功能可以改善您的表达能力、查询性能以及错误管理功能。本文重点介绍几个概念新颖且互相联系的增强功能，并且通过实际示例演示这些功能。本文并未讨论所有新增的 Transact-SQL 功能。
　　
　　预备知识：目标读者应该能够熟练使用 Transact-SQL 进行特定查询以及将其作为 Microsoft sql server(WINDOWS平台上强大的数据库平台) 2000 中应用程序的组件。
　　
　　改善查询的表达能力和 DRI 支持
　　
　　本节介绍下列新增的关系功能和增强功能：
　　
　　新增的排序函数
　　
　　新增的基于常见表表达式 (CTE) 的递归查询
　　
　　新增的 PIVOT 和 APPLY 关系运算符
　　
　　声明性引用完整性 (DRI) 增强
　　
　　排序函数
　　
　　sql server(WINDOWS平台上强大的数据库平台) 2005 引入了四个新的排序函数：ROW_NUMBER、RANK、DENSE_RANK 和 NTILE。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。您可能发现这些新函数有用的典型方案包括：将连续整数分配给结果行，以便进行表示、分页、计分和绘制直方图。
　　
　　Speaker Statistics 方案
　　
　　下面的 Speaker Statistics 方案将用来讨论和演示不同的函数和它们的子句。大型计算会议包括三个议题：数据库、开发和系统管理。十一位演讲者在会议中发表演讲，并且为他们的讲话获得范围为 1 到 9 的分数。结果被总结并存储在下面的 SpeakerStats 表中：
　　
　　USE tempdb -- or your own test database
　　CREATE TABLE SpeakerStats
　　(
　　　speaker　　　　VARCHAR(10) NOT NULL Prima(最完善的虚拟主机管理系统)RY KEY,
　　　track　　　　　VARCHAR(10) NOT NULL,
　　　score　　　　　INT　　　　 NOT NULL,
　　　pctfilledevals INT　　　　 NOT NULL,
　　　numsessions　　INT　　　　 NOT NULL
　　)
　　
　　SET NOCOUNT ON
　　INSERT INTO SpeakerStats VALUES('Dan',　　 'Sys', 3, 22, 4)
　　INSERT INTO SpeakerStats VALUES('Ron',　　 'Dev', 9, 30, 3)
　　INSERT INTO SpeakerStats VALUES('Kathy',　 'Sys', 8, 27, 2)
　　INSERT INTO SpeakerStats VALUES('Suzanne', 'DB',　9, 30, 3)
　　INSERT INTO SpeakerStats VALUES('Joe',　　 'Dev', 6, 20, 2)
　　INSERT INTO SpeakerStats VALUES('Robert',　'Dev', 6, 28, 2)
　　INSERT INTO SpeakerStats VALUES('Mike',　　'DB',　8, 20, 3)
　　INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
　　INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
　　INSERT INTO SpeakerStats VALUES('Brian',　 'Sys', 7, 22, 3)
　　INSERT INTO SpeakerStats VALUES('Kevin',　 'DB',　7, 25, 4)
　　
　　每个演讲者都在该表中具有一个行，其中含有该演讲者的名字、议题、平均得分、填写评价的与会者相对于参加会议的与会者数量的百分比以及该演讲者发表演讲的次数。本节演示如何使用新的排序函数分析演讲者统计数据以生成有用的信息。
　　
　　语义
　　
　　全部四个排序函数都遵循类似的语法模式：
　　
　　排序函数
　　
　　() OVER(
　　　[PARTITION BY ]
　　　ORDER BY )
　　
　　该函数只能在查询的两个子句中指定 ― 在 SELECT 子句或 ORDER BY 子句中。以下各节详细讨论不同的函数。
　　
　　ROW_NUMBER
　　
　　ROW_NUMBER 函数使您可以向查询的结果行提供连续的整数值。例如，假设您要返回所有演讲者的 speaker、track 和 score，同时按照 score 降序向结果行分配从 1 开始的连续值。以下查询通过使用 ROW_NUMBER 函数并指定 OVER (ORDER BY score DESC) 生成所需的结果：
　　
　　SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
　　　speaker, track, score
　　FROM SpeakerStats
　　ORDER BY score DESC
　　
　　以下为结果集：
　　
　　rownum speaker　　track　　　score
　　------ ---------- ---------- -----------
　　1　　　Jessica　　Dev　　　　9
　　2　　　Ron　　　　Dev　　　　9
　　3　　　Suzanne　　DB　　　　 9
　　4　　　Kathy　　　Sys　　　　8
　　5　　　Michele　　Sys　　　　8
　　6　　　Mike　　　 DB　　　　 8
　　7　　　Kevin　　　DB　　　　 7
　　8　　　Brian　　　Sys　　　　7
　　9　　　Joe　　　　Dev　　　　6
　　10　　 Robert　　 Dev　　　　6
　　11　　 Dan　　　　Sys　　　　3
　　
　　得分最高的演讲者获得行号 1，得分最低的演讲者获得行号 11。ROW_NUMBER 总是按照请求的排序为不同的行生成不同的行号。请注意，如果在 OVER() 选项中指定的 ORDER BY 列表不唯一，则结果是不确定的。这意味着该查询具有一个以上正确的结果；在该查询的不同调用中，可能获得不同的结果。例如，在我们的示例中，有三个不同的演讲者获得相同的最高得分 (9)：Jessica、Ron 和 Suzanne。由于 sql server(WINDOWS平台上强大的数据库平台) 必须为不同的演讲者分配不同的行号，因此您应当假设分别分配给 Jessica、Ron 和 Suzanne 的值 1、2 和 3 是按任意顺序分配给这些演讲者的。如果值 1、2 和 3 被分别分配给 Ron、Suzanne 和 Jessica，则结果应该同样正确。
　　
　　如果您指定一个唯一的 ORDER BY 列表，则结果总是确定的。例如，假设在演讲者之间出现得分相同的情况时，您希望使用最高的 pctfilledevals 值来分出先后。如果值仍然相同，则使用最高的 numsessions 值来分出先后。最后，如果值仍然相同，则使用最低词典顺序 speaker 名字来分出先后。由于 ORDER BY 列表 ― score、pctfilledevals、numsessions 和 speaker ― 是唯一的，因此结果是确定的：
　　
　　SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,
　　　　　　　　　　　　　　　 numsessions DESC, speaker) AS rownum,
　　　speaker, track, score, pctfilledevals, numsessions
　　FROM SpeakerStats
　　ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker
　　
　　以下为结果集：
　　
　　rownum speaker　　track　　　score　　　 pctfilledevals numsessions
　　------ ---------- ---------- ----------- -------------- -----------
　　1　　　Ron　　　　Dev　　　　9　　　　　 30　　　　　　 3
　　2　　　Suzanne　　DB　　　　 9　　　　　 30　　　　　　 3
　　3　　　Jessica　　Dev　　　　9　　　　　 19　　　　　　 1
　　4　　　Michele　　Sys　　　　8　　　　　 31　　　　　　 4
　　5　　　Kathy　　　Sys　　　　8　　　　　 27　　　　　　 2
　　6　　　Mike　　　 DB　　　　 8　　　　　 20　　　　　　 3
　　7　　　Kevin　　　DB　　　　 7　　　　　 25　　　　　　 4
　　8　　　Brian　　　Sys　　　　7　　　　　 22　　　　　　 3
　　9　　　Robert　　 Dev　　　　6　　　　　 28　　　　　　 2
　　10　　 Joe　　　　Dev　　　　6　　　　　 20　　　　　　 2
　　11　　 Dan　　　　Sys　　　　3　　　　　 22　　　　　　 4
　　
　　新的排序函数的重要好处之一是它们的效率。sql server(WINDOWS平台上强大的数据库平台) 的优化程序只需要扫描数据一次，以便计算值。它完成该工作的方法是：使用在排序列上放置的索引的有序扫描，或者，如果未创建适当的索引，则扫描数据一次并对其进行排序。
　　
　　另一个好处是语法的简单性。为了让您感受一下通过使用在 sql server(WINDOWS平台上强大的数据库平台) 的较低版本中采用的基于集的方法来计算排序值是多么困难和低效，请考虑下面的 sql server(WINDOWS平台上强大的数据库平台) 2000 查询，它返回与上一个查询相同的结果：
　　
　　SELECT
　　　(SELECT COUNT(*)
　　　 FROM SpeakerStats AS S2
　　　 WHERE S2.score > S1.score
　　　　 OR (S2.score = S1.score
　　　　　　 AND S2.pctfilledevals > S1.pctfilledevals)
　　　　 OR (S2.score = S1.score
　　　　　　 AND S2.pctfilledevals = S1.pctfilledevals
　　　　　　 AND S2.numsessions > S1.numsessions)
　　　　 OR (S2.score = S1.score
　　　　　　 AND S2.pctfilledevals = S1.pctfilledevals
　　　　　　 AND S2.numsessions = S1.numsessions
　　　　　　 AND S2.speaker < S1.speaker)) + 1 AS rownum,
　　　speaker, track, score, pctfilledevals, numsessions
　　FROM SpeakerStats AS S1
　　ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker
　　
　　该查询显然比 sql server(WINDOWS平台上强大的数据库平台) 2005 查询复杂得多。此外，对于 SpeakerStats 表中的每个基础行，sql server(WINDOWS平台上强大的数据库平台) 都必须扫描该表的另一个实例中的所有匹配行。对于基础表中的每个行，平均大约需要扫描该表的一半（最少）行。sql server(WINDOWS平台上强大的数据库平台) 2005 查询的性能恶化是线性的，而 sql server(WINDOWS平台上强大的数据库平台) 2000 查询的性能恶化是指数性的。即使是在相当小的表中，性能差异也是显著的。例如，请测试下列查询的性能，它们查询 AdventureWorks 数据库中的 SalesOrderHeader 表，以便按照 SalesOrderID 顺序计算销售定单的行数。SalesOrderHeader 表具有 31,465 行。第一个查询使用 sql server(WINDOWS平台上强大的数据库平台) 2005 ROW_NUMBER 函数，而第二个查询使用 sql server(WINDOWS平台上强大的数据库平台) 2000 子查询技术：
　　
　　-- sql server(WINDOWS平台上强大的数据库平台) 2005 query
　　SELECT SalesOrderID,
　　　ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS rownum
　　FROM Sal