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

首先，让我们来做一个简单的实验

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环境下对视图强制排序

https://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的排序。