SQL Server 分頁

今天做網站時，用到了分頁，雖然.NET本身有分頁的控制項，然而效率不敢恭維；於是就用SQLSERVER資料庫的預存程序來實現分頁，總結了以下四種方案並進行比較，供朋友們參考，少走彎路啊，呵呵。

方案一（利用ID大於多少和SELECT TOP分頁）語句形式(SQLSERVER 2000)：

CREATE procedure cfems_getinfolist(@pageindex int,@pagesize int) as declare @sqlstr varchar(1000) set @sqlstr='select top '+cast(@pagesize as varchar)+'* from count_main where id>='+ '(select max(id) from (select top '+cast(1+@pagesize*(@pageindex-1) as varchar)+' id '+ 'from count_main order by id) AS T) order by id' begin  exec (@sqlstr) end; GO

 語句形式（SQLSERVER 2005）:

CREATE procedure cfems_getinfolist(@pageindex int,@pagesize int) as BEGIN select top @pagesize * from count_main where id>=(select max(id) from (select top 1+@pagesize*(@pageindex-1) id from count_main order by id) AS T) order by id end; GO

 方案二（利用Not In和SELECT TOP分頁）語句形式：

SELECT TOP 10 *  FROM TestTable  WHERE (ID NOT IN            (SELECT TOP 20 id           FROM TestTable           ORDER BY id))  ORDER BY ID    SELECT TOP 頁大小 *  FROM TestTable  WHERE (ID NOT IN            (SELECT TOP 頁大小*頁數 id           FROM 表           ORDER BY id))  ORDER BY ID

  方案三（使用類似oracle的rownum功能來實現分頁）

create procedure cfems_test(@pageindex int,@pagesize int) as begin select identity(int,1,1) rownum,* into test from (select   top   100   percent   *   from   count_main) T; select * from test where rownum>(@pageindex-1)*@pagesize and rownum<=@pageindex*@pagesize drop table test; end;

 

 

方案四（利用SQL的遊標預存程序分頁）

create  procedure SqlPager
 @sqlstr nvarchar(4000), --查詢字串
 @currentpage int, --第N頁
 @pagesize int --每頁行數
 as
 set nocount on
 declare @P1 int, --P1是遊標的id
  @rowcount int
 exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
 select ceiling(1.0*@rowcount/@pagesize) as 總頁數--,@rowcount as 總行數,@currentpage as 當前頁 
 set @currentpage=(@currentpage-1)*@pagesize+1
 exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
 exec sp_cursorclose @P1
 set nocount off

 

通過SQL 查詢分析器，顯示比較：我的結論是:
 分頁方案一、二：效率都比較高，40多萬的資料量查詢最後幾頁2秒鐘就可以出來，尤其推薦方案一；

 分頁方案三、四：效率最差，不建議使用