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秒钟就可以出来，尤其推荐方案一；

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