巧用SQL Server（Ranking）实现view的排序功能

zpjx

http://blogs.msdn.com/b/apgcdsd/archive/2011/06/07/sql-server-ranking-view.aspx
微软亚太区数据库技术支持组官方博客 英文博客
微软亚太区数据库技术支持组 官方博客



Search this blog  Search all blogs
最热 微软中文博客Live Now on Server & Tools Blogs Feed
免费获得Windows Server 2012专家培训，就在本月！
在几分钟内轻松地将您的LightSwitch应用程序部署到Azure网站
如何从C#桌面应用程序中调用Windows 8的WinRT API- WinRT 图表
Subscribe
Comments
Contact
Menu
Blog Home
Atom
翻译此页

Microsoft® Translator
Tags
linked server Oracle SQL Server transaction replication 内存 命令管道 性能优化 数据库引擎 死锁 游标 证书 连接
More ▼
Archives
April 2014 (2)
February 2014 (1)
October 2013 (4)
September 2013 (1)
August 2013 (1)
More ▼
巧用SQL Server（Ranking）实现view的排序功能

APGC DSD Team 6 Jun 2011 8:28 PM 0
首先，让我们来做一个简单的实验

Create view test_view

As

Select top 100 percent * from table order by ID



Create view test_view1

As

Select top 99  percent * from table order by ID



测试部分：

a. select * from test_view where id in (9,20)  

b. select * from test_view1 where id in (9,20)

其中table是一个超过10000行数据的表格，其中在ID栏上已建有索引。

通过如下语句select count(distinct id) from table，返回值为9000，这个结果说明index选择性很好。

结果发现，



测试语句a：

在SQL Server 2000环境下使用索引做查找并返回排序后的结果；

而在SQL Server 2008中不进行排序，但仍然使用索引进行查找、



测试语句b：

SQL 2000 和SQL2008始终使用全表扫描，返回排序后的结果，即使它只有2行返回值。



我们先解释语句a在2000和2008上得到不同结果的原因。在SQL Server 2008中，优化器的算法做了一些修改，如果发现top 100%的情况出现，我们会自动忽略top + Order by，因为top 100%本来就是返回全表数据。在2000上是没有这个智能优化的，所以2000会严格按照语法的格式和先后顺序执行。



我们知道，在view中是没有办法使用order by子句的，除非我们加上top 关键字。但是现在top 100%又会被自动忽略，那么，如果在SQL Server2008中希望得到view排序的结果，应该怎么做呢？

这里可以分享一篇文章，关于在SQL Server2005和SQL Server2008环境下对视图强制排序

http://support.microsoft.com/default.aspx?scid=kb;en-us;926292&sd=rss&spid=2855



解决方法就是 用top 99%。

但是同时带来另一个问题，它将执行如Select top 99  percent * from table order by ID的执行计划。也就是说，SQL Server会在视图中使用的表上做全表扫描。

我们能不能找到一种高性能的得到有序返回集的方法呢？

排序参数（Ranking）虽然不是为此而设计，却能有效的解决这一问题

使用方法实例如：

Create view test_view2

SELECT *       ,

       RANK() OVER (PARTITION BY id  ORDER BY other_column) AS RANKing

FROM table



这个写法的意思是按照ID做partition，那么自然就会按照ID做排序。

Select * from test_view2 where id in (9,20)

如此，建立的视图和查找的命令都可以使用ID栏上的索引，并依据ID对结果集排序。美中不足是，这种排序只能是基于ASC的排序。