巧用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環境下對檢視強制排序

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25175503/viewspace-704917/，如需轉載，請註明出處，否則將追究法律責任。