转:SQL Server 2005新功能详解

(这些新功能确实不错,特地找了一篇比较详细介绍.)

 

今天整理了一下SQL Server 2005的新增功能，自己学习了以后进行了总结，一共有四个新的排序函数(ROW_NUMBER、RANK、DENSE_RANK 和 NTILE)和三个其他新增功能(TOP表达式，Try...Catch,通用表达式CTE)

为了详细进行讲解，这儿建立一张表StuTest：

create table stutest(pid int primary key,name varchar(50) not null,age int not null,sex varchar(10) not null,score float not null)

然后插入一些测试数据：

insert into stutest values('yingzi',23,'female',89.5)
insert into stutest values('zhudan',22,'female',78.5)
insert into stutest values('ludi',24,'male',90.3)
insert into stutest values('yujue',21,'female',84.5)
insert into stutest values('shiche',20,'male',81.5)
insert into stutest values('adfasd',23,'female',45.5)
insert into stutest values('dfad',23,'male',98.5)
insert into stutest values('ertd',23,'female',80.5)
insert into stutest values('fgfg',22,'female',73.5)
insert into stutest values('jkghj',24,'male',94.3)
insert into stutest values('trts',21,'female',85.5)
insert into stutest values('bvbx',20,'male',87.5)
insert into stutest values('adf',23,'female',49.5)
insert into stutest values('ughdgh',23,'male',91.5)
insert into stutest values('lkgj',23,'female',70.5)
insert into stutest values('grt',21,'female',85.5)
insert into stutest values('loiut',20,'male',87.5)
insert into stutest values('erer',23,'female',49.5)
insert into stutest values('rqeq',23,'male',91.5)
insert into stutest values('trwer',23,'female',70.5)

执行SQL语句：select * from stutest

结果如下图：

（1）ROW_NUMBER()函数。ROW_NUMBER 函数使您可以向查询的结果行提供连续的整数值。

例子：假设您要返回所有学生的 age、name、sex和 score，同时按照 score 降序向结果行分配从 1 开始的连续值。SQL语句如下：

select row_number() over(order by score desc) as scorerank,age,name,sex,score from stutest                                                     order by score desc

结果如下：

ROW_NUMBER 总是按照请求的排序为不同的行生成不同的行号。请注意，如果在 OVER() 选项中指定的 ORDER BY 列表不唯一，则结果是不确定的。这意味着该查询具有一个以上正确的结果；在该查询的不同调用中，可能获得不同的结果。如果您指定一个唯一的 ORDER BY 列表，则结果总是确定的。例如，假设在学生之间出现得分相同的情况时，您希望使用最高的age值来分出先后。如果值仍然相同，则使用最低词典顺序sex值来分出先后。最后，如果值仍然相同，则使用最低词典顺序name名字来分出先后。由于 ORDER BY 列表——score、age、sex和 name——是唯一的，因此结果是确定的。SQL语句如下：

select row_number() over(order by score desc,age desc,sex,name) as scorerank,age,name,sex,score from stutest
order by score desc,age desc,sex,name

结果如下图：

新的排序函数的重要好处之一是它们的效率。SQL Server 的优化程序只需要扫描数据一次，以便计算值。它完成该工作的方法是：使用在排序列上放置的索引的有序扫描，或者，如果未创建适当的索引，则扫描数据一次并对其进行排序。另一个好处是语法的简单性。

行号的一个典型应用是通过查询结果分页。给定页大小（以行数为单位）和页号，需要返回属于给定页的行。例如，假设您希望按照“score DESC, age”顺序从 StuTest表中返回第二页的行，并且假定页大小为三行。下面的查询首先按照指定的排序计算派生表 D 中的行数，然后只筛选行号为 4 到 6 的行（它们属于第二页）.SQL语句如下：

select * from                                                    (select row_number() over(order by score desc,age desc,sex,name) as scorerank,age,name,sex,score from stutest) as d
where scorerank between 4 and 6

结果如下图：

用更一般的术语表达就是，给定 @pagenum 变量中的页号和 @pagesize 变量中的页大小，以下查询返回属于预期页的行：

declare @pagenum as int,@pagesize as int
set @pagenum=2
set @pagesize=3
select * from (select row_number() over(order by score desc,age desc,sex,name) as scorerank,age,name,sex,score from stutest) as d
where scorerank between (@pagenum-1)*@pagesize+1 and @pagenum*@pagesize

结果同上：

上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了。但是，当用户发出多个请求时，该方法就不能满足需要了，因为该查询的每个调用都 需要您对表进行完整扫描，以便计算行号。当用户可能反复请求不同的页时，为了更有效地进行分页，请首先用所有基础表行（包括计算得到的行号）填充一个临时表，并且对包含这些行号的列进行索引：

select row_number() over(order by score desc,age desc,sex,name) as scorerank,*
into #stutestRN from stutest
create unique clustered index idx_uc_scorerank on #stutestRN(scorerank)

然后，对于所请求的每个页，发出以下查询：

declare @pagenum as int,@pagesize as int
set @pagenum=2
set @pagesize=3
select * from #stutestRN
where scorerank between (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

结果同上：

可以在行组内部独立地计算排序值，而不是为作为一个组的所有表行计算排序值。为此，请使用 PARTITION BY 子句，并且指定一个表达式列表，以标识应该为其独立计算排序值的行组。例如，以下查询按照“score desc, age desc,name”顺序单独分配每个sex内部的行号：

select sex,row_number() over(
partition by sex order by score desc,age,name) as pos,age,name,score
from stutest order by sex,score desc,age,name

结果如下图：

（2）RANK、DENSE_RANK函数。RANK 和 DENSE_RANK 函数非常类似于 ROW_NUMBER 函数，因为它们也按照指定的排序提供排序值，而且可以根据需要在行组（分段）内部提供。但是，与 ROW_NUMBER 不同的是，RANK 和 DENSE_RANK 向在排序列中具有相同值的行分配相同的排序。当 ORDER BY 列表不唯一，并且您不希望为在 ORDER BY 列表中具有相同值的行分配不同的排序时，RANK 和 DENSE_RANK 很有用。RANK 和 DENSE_RANK 的用途以及两者之间的差异可以用示例进行最好的解释。以下查询按照 score DESC 顺序计算不同学生的行号、排序和紧密排序值：

select name,age,sex,score,                                      row_number() over(order by score desc) as rownum,
rank() over(order by score desc) as rnk,                             dense_rank() over(order by score desc) as drnk
from stutest order by score desc

结果如下图：

（3）NTILE函数。NTILE使您可以按照指定的顺序，将查询的结果行分散到指定数量的组(tile)中，每个行组都获得不同的号码：第一组为1，第二组为2，等等。您可以在函数名称后面的括号中指定所请求的组号，在OVER选项的ORDER BY子句中指定所请求的排序。组中的行数被计算为total_num_rows/num_groups。如果有余数n,则前面n个组获得一个附加行。因此，可能不会所有组都获得相等数量的行，但是组大小最大只可能相差一行。例如，以下查询按照score降序将三个组号分配给不同的行。

select name,age,sex,score,
row_number() over(order by score desc) as rownum,
ntile(3) over(order by score desc) as tile
from stutest order by score desc

结果如下图：

通过该信息可以生成直方图，并且将项目均匀分布到每个梯级。在我们的示例中，第一个梯级表示具有最高得分的学生，第二个梯级表示具有中等得分的学生，第三个梯级表示具有最低得分的学生。可以使用CASE表达式为组号提供说明性的有意义的备选含义：

select name,age,sex,score,
case ntile(3) over(order by score desc)
when 1 then 'High'
when 2 then 'Medium'
when 3 then 'Low'
end as scoregrade
from stutest

结果如下图：

（5）TOP表达式。TOP表达式可以取得指定的前N条记录。例如：取出StuTest表中前10条记录：select top(10) * from stutest

结果如下图：

（6）Try...Catch。SQL Server 2005新增了异常处理功能。

例子：                                                           SET XACT_ABORT ON
BEGIN TRY
    begin tran
        insert into stutest values('rtwrt',27,'female',76.5)
    commit tran
    print 'commited'
END TRY
BEGIN CATCH
    rollback    
    print 'rolled back'
END CATCH

结果：

（7）通用表达式CTE。通过表达式可免除你过去创建临时表的麻烦。

with stutest_temp as(
    select *,ROW_NUMBER() OVER(order by score desc) as row from stutest
)
select name, age from stutest_temp where row between 10 and 20

结果如下图：

以上是我的总结。通过学习，自己对SQL Server 2005的新功能有了进一步的了解，可以使用自如。