[ZT]利用Sql Server 2005的新函數ROW_NUMBER()寫的高效分頁存儲過程

文章來源：網絡

微軟最新發佈的MSSQL2005,對TSQL進行了小規模的加強，有些函數的確非常實用。就比如ROW_NUMBER()函數，實現了原先一直要用存儲過程來進行大數據分頁的功能。現在有了這個函數，只要簡單一個語句就能解決任何情況下的數據分頁，不管你要排序的字段是否唯一。
　　個人感覺，這個函數應該能再進的一步優化性能，當然這個工作不是我們所能為，MS不去開發，我們就沒辦法了。

 程序代碼

　select * from (Select ROW_NUMBER() OVER (order by id desc) as pos from [table] where year(birth)=1981) as sp where pos between 1 and 5

　　實際底層的工作邏輯不是很清楚，假如是每次執行都需要進行全表排序的話，那真的是太浪費資源了。即使有了索引，不需要再進行全表排序，是否必然會進行一次ROW_NUMBER()函數的全表運算呢？
　　我們假設索引都有一個索引表的rowID，就類似於ROW_NUMBER()產生的值。那麼事先已經有過索引的，是不是就能直接根據索引表的rowID進行高效分頁呢？這種方法應該是可行，具體MSSQL2005中是否有這個功能，還需要進一步學習。
　　按照以上方法，應該能簡單寫出分頁的SQL語句：

 程序代碼

Select from [table] where year(birth)=1981 and ROW_NUMBER() between 1 and 5 order by id desc

　　分析器執行此語句時，對於ROW_NUMBER()函數，首先檢查是否有索引匹配，若匹配，則直接訪問索引中的rowID進行條件讀取，若是無匹配索引，則根據當前排序條件重新進行一次全表的ROW_NUMBER()計算。
不過這個方法可能會增加索引的工作強度，由於每次的索引重建都需要進行一次全表ROW_NUMBER()運算。不過由於索引是多字段及多方向的，例如，一個索引有2個字段，那麼就必然需要4個ROW_NUMBER()表，如果索引字段更多，就變得很複雜了。因此，可以選擇性的為某些字段的排序方向保存rowID。


　　下面是我簡單寫的一個存儲過程，希望對你有用，相對之前的存儲分頁，性能自然也是非常高效的。個人覺得用存儲過程調用，不管是性能上還是使用上，都應該更方便一些。如果你覺得直接程序中用SQL語句調用，那也是完全沒問題的。

 程序代碼

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[ShowPage]
@tblName   varchar(255),       -- 表名
@strGetFields varchar(1000) = ''*'',  -- 需要返回的列
@strOrder varchar(255)='''',      -- 排序的字段名
@PageSize   int = 10,          -- 頁尺寸
@PageIndex  int = 1,           -- 頁碼
@strWhere  varchar(1500) = ''''  -- 查詢條件 (注意: 不要加 where)
AS

declare @strSQL   varchar(5000)

if @strWhere !=''''
  set @strWhere='' where ''+@strWhere

set @strSQL=''Select * FROM (Select ROW_NUMBER() OVER (''+@strOrder+'') AS pos,''+@strGetFields+'' FROM [''+@tblName+'']''+@strWhere+'') AS sp Where pos BETWEEN ''+str((@PageIndex-1)*@PageSize+1)+'' AND ''+str(@PageIndex*@PageSize)

exec (@strSQL)