我首先写了五个常用存储过程：

1，利用select top 和select not in进行分页，具体代码如下：

CREATE PROCEDURE Proc_paged_with_notin --利用select top and select not in (@pageIndex INT,--页索引 @pageSize INT --每页记录数 ) AS BEGIN SET nocount ON; DECLARE @timediff DATETIME --耗时 DECLARE @sql NVARCHAR(500) SELECT @timediff = Getdate() SET @sql='select top ' + Str(@pageSize) + ' * from tb_TestTable where(ID not in(select top ' + Str(@pageSize*@pageIndex) + ' id from tb_TestTable order by ID ASC)) order by ID' EXECUTE(@sql) --因select top后不支技直接接参数，所以写成了字符串@sql SELECT Datediff(ms, @timediff, Getdate()) AS 耗时 SET nocount OFF; END

2,利用select top 和 select max(列键)

CREATE PROCEDURE Proc_paged_with_selectmax --利用select top and select max(列) (@pageIndex INT,--页索引 @pageSize INT --页记录数 ) AS BEGIN SET nocount ON; DECLARE @timediff DATETIME DECLARE @sql NVARCHAR(500) SELECT @timediff = Getdate() SET @sql='select top ' + Str(@pageSize) + ' * From tb_TestTable where(ID>(select max(id) From (select top ' + Str(@pageSize*@pageIndex) + ' id From tb_TestTable order by ID) as TempTable)) order by ID' EXECUTE(@sql) SELECT Datediff(ms, @timediff, Getdate()) AS 耗时 SET nocount OFF; END

3,利用select top和中间变量–此方法因网上有人说效果最佳，所以贴出来一同测试

CREATE PROCEDURE Proc_paged_with_midvar --利用ID>最大ID值和中间变量 (@pageIndex INT, @pageSize INT) AS DECLARE @count INT DECLARE @ID INT DECLARE @timediff DATETIME DECLARE @sql NVARCHAR(500) BEGIN SET nocount ON; SELECT @count = 0, @ID = 0, @timediff = Getdate() SELECT @count = @count + 1, @ID = CASE WHEN @count <= @pageSize * @pageIndex THEN id ELSE @ID END FROM tb_testtable ORDER BY id SET @sql='select top ' + Str(@pageSize) + ' * from tb_testTable where ID>' + Str(@ID) EXECUTE(@sql) SELECT Datediff(ms, @timediff, Getdate()) AS 耗时 SET nocount OFF; END

4,利用Row_number() 此方法为sql server 2005中新的方法,利用Row_number()给数据行加上索引

CREATE PROCEDURE Proc_paged_with_rownumber --利用SQL 2005中的Row_number() (@pageIndex INT, @pageSize INT) AS DECLARE @timediff DATETIME BEGIN SET nocount ON; SELECT @timediff = Getdate() SELECT * FROM (SELECT *, Row_number() OVER( ORDER BY id ASC) AS IDRank FROM tb_testtable) AS IDWithRowNumber WHERE idrank > @pageSize * @pageIndex AND idrank < @pageSize * ( @pageIndex + 1 ) SELECT Datediff(ms, @timediff, Getdate()) AS 耗时 SET nocount OFF; END

5，利用临时表及Row_number

CREATE PROCEDURE Proc_cte --利用临时表及Row_number (@pageIndex INT,--页索引 @pageSize INT --页记录数 ) AS SET nocount ON; DECLARE @ctestr NVARCHAR(400) DECLARE @strSql NVARCHAR(400) DECLARE @datediff DATETIME BEGIN SELECT @datediff = Getdate() SET @ctestr='with Table_CTE as (select ceiling((Row_number() over(order by ID ASC))/' + Str(@pageSize) + ') as page_num,* from tb_TestTable)'; SET @strSql=@ctestr + ' select * From Table_CTE where page_num=' + Str(@pageIndex) END BEGIN EXECUTE Sp_executesql @strSql SELECT Datediff(ms, @datediff, Getdate()) SET nocount OFF; END

OK,至此，存储过程创建完毕，我们分别在每页10条数据的情况下在第2页，第1000页，第10000页，第100000页，第199999页进行测试，耗时单位：ms 每页测试5次取其平均值 存过 第2页耗时 第1000页耗时 第10000页耗时 第100000页耗时 第199999页耗时 效率排行

序号存过第2页耗时第1000页耗时第10000页耗时第100000页耗时第199999页耗时效率排行1用not in0ms16ms47ms475ms953ms32用select max5ms16ms35ms325ms623ms13中间变量966ms970ms960ms945ms933ms54row_number0ms0ms34ms365ms710ms24临时表780ms796ms798ms780ms805ms4

测试结果显示：select max >row_number>not in>临时表>中间变量 于是我对效率最高的select max方法用2分法进行了扩展，代码取自互联网，我修改了ASC排序时取不到值的BUG，测试结果: 2分法 156ms 156ms 180ms 470ms 156ms 1* 从测试结果来看，使用2分法确实可以提高效率并使效率更为稳定，我又增加了第159999页的测试，用时仅296ms，效果相当的不错！ 下面是2分法使用select max的代码，已相当完善。

--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/ ALTER PROCEDURE Proc_paged_2part_selectmax (@tblName NVARCHAR(200), ----要显示的表或多个表的连接 @fldName NVARCHAR(500) = '*', ----要显示的字段列表 @pageSize INT = 10, ----每页显示的记录个数 @page INT = 1, ----要显示那一页的记录 @fldSort NVARCHAR(200) = NULL, ----排序字段列表或条件 @Sort BIT = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ') @strCondition NVARCHAR(1000) = NULL, ----查询条件,不需where @ID NVARCHAR(150), ----主表的主键 @Dist BIT = 0, ----是否添加查询字段的 DISTINCT 默认0不添加/1添加 @pageCount INT = 1 output, ----查询结果分页后的总页数 @Counts INT = 1 output ----查询到的记录数 ) AS SET nocount ON DECLARE @sqlTmp NVARCHAR(1000) ----存放动态生成的SQL语句 DECLARE @strTmp NVARCHAR(1000) ----存放取得查询结果总数的查询语句 DECLARE @strID NVARCHAR(1000) ----存放取得查询开头或结尾ID的查询语句 DECLARE @strSortType NVARCHAR(10) ----数据排序规则A DECLARE @strFSortType NVARCHAR(10) ----数据排序规则B DECLARE @SqlSelect NVARCHAR(50) ----对含有DISTINCT的查询进行SQL构造 DECLARE @SqlCounts NVARCHAR(50) ----对含有DISTINCT的总数查询进行SQL构造 DECLARE @timediff DATETIME --耗时测试时间差 SELECT @timediff = Getdate() IF @Dist = 0 BEGIN SET @SqlSelect = 'select ' SET @SqlCounts = 'Count(*)' END ELSE BEGIN SET @SqlSelect = 'select distinct ' SET @SqlCounts = 'Count(DISTINCT ' + @ID + ')' END IF @Sort = 0 BEGIN SET @strFSortType=' ASC ' SET @strSortType=' DESC ' END ELSE BEGIN SET @strFSortType=' DESC ' SET @strSortType=' ASC ' END --------生成查询语句-------- --此处@strTmp为取得查询结果数量的语句 IF @strCondition IS NULL OR @strCondition = '' --没有设置显示条件 BEGIN SET @sqlTmp = @fldName + ' From ' + @tblName SET @strTmp = @SqlSelect + ' @Counts=' + @SqlCounts + ' FROM ' + @tblName SET @strID = ' From ' + @tblName END ELSE BEGIN SET @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition SET @strTmp = @SqlSelect + ' @Counts=' + @SqlCounts + ' FROM ' + @tblName + ' where (1>0) ' + @strCondition SET @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition END ----取得查询结果总数量----- EXEC Sp_executesql @strTmp, N'@Counts int out ', @Counts out DECLARE @tmpCounts INT IF @Counts = 0 SET @tmpCounts = 1 ELSE SET @tmpCounts = @Counts --取得分页总数 SET @pageCount=( @tmpCounts + @pageSize - 1 ) / @pageSize /**//**//**//**当前页大于总页数 取最后一页**/ IF @page > @pageCount SET @page=@pageCount --/*-----数据分页2分处理-------*/ DECLARE @pageIndex INT --总数/页大小 DECLARE @lastcount INT --总数%页大小 SET @pageIndex = @tmpCounts / @pageSize SET @lastcount = @tmpCounts%@pageSize IF @lastcount > 0 SET @pageIndex = @pageIndex + 1 ELSE SET @lastcount = @pagesize --//***显示分页 IF @strCondition IS NULL OR @strCondition = '' --没有设置显示条件 BEGIN IF @pageIndex < 2 OR @page <= @pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 BEGIN IF @page = 1 SET @strTmp=@SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' order by ' + @fldSort + ' ' + @strFSortType ELSE BEGIN IF @Sort = 1 BEGIN SET @strTmp=@SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @ID + ' <(select min(' + @ID + ') from (' + @SqlSelect + ' top ' + Cast(@pageSize*(@page-1) AS VARCHAR(20 )) + ' ' + @ID + ' from ' + @tblName + ' order by ' + @fldSort + ' ' + @strFSortType + ') AS TBMinID)' + ' order by ' + @fldSort + ' ' + @strFSortType END ELSE BEGIN SET @strTmp=@SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @ID + ' >(select max(' + @ID + ') from (' + @SqlSelect + ' top ' + Cast(@pageSize*(@page-1) AS VARCHAR(20 )) + ' ' + @ID + ' from ' + @tblName + ' order by ' + @fldSort + ' ' + @strFSortType + ') AS TBMinID)' + ' order by ' + @fldSort + ' ' + @strFSortType END END END ELSE BEGIN SET @page = @pageIndex - @page + 1 --后半部分数据处理 IF @page <= 1 --最后一页数据显示 SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top ' + Cast(@lastcount AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType ELSE IF @Sort = 1 BEGIN SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @ID + ' >(select max(' + @ID + ') from(' + @SqlSelect + ' top ' + Cast(@pageSize*(@page-2)+@lastcount AS VARCHAR( 20) ) + ' ' + @ID + ' from ' + @tblName + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TBMaxID)' + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType END ELSE BEGIN SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @ID + ' <(select min(' + @ID + ') from(' + @SqlSelect + ' top ' + Cast(@pageSize*(@page-2)+@lastcount AS VARCHAR( 20) ) + ' ' + @ID + ' from ' + @tblName + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TBMaxID)' + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType END END END ELSE --有查询条件 BEGIN IF @pageIndex < 2 OR @page <= @pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 BEGIN IF @page = 1 SET @strTmp=@SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where 1=1 ' + @strCondition + ' order by ' + @fldSort + ' ' + @strFSortType ELSE IF( @Sort = 1 ) BEGIN SET @strTmp=@SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @ID + ' <(select min(' + @ID + ') from (' + @SqlSelect + ' top ' + Cast(@pageSize*(@page-1) AS VARCHAR(20)) + ' ' + @ID + ' from ' + @tblName + ' where (1=1) ' + @strCondition + ' order by ' + @fldSort + ' ' + @strFSortType + ') AS TBMinID)' + ' ' + @strCondition + ' order by ' + @fldSort + ' ' + @strFSortType END ELSE BEGIN SET @strTmp=@SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @ID + ' >(select max(' + @ID + ') from (' + @SqlSelect + ' top ' + Cast(@pageSize*(@page-1) AS VARCHAR(20)) + ' ' + @ID + ' from ' + @tblName + ' where (1=1) ' + @strCondition + ' order by ' + @fldSort + ' ' + @strFSortType + ') AS TBMinID)' + ' ' + @strCondition + ' order by ' + @fldSort + ' ' + @strFSortType END END ELSE BEGIN SET @page = @pageIndex - @page + 1 --后半部分数据处理 IF @page <= 1 --最后一页数据显示 SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top ' + Cast(@lastcount AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where (1=1) ' + @strCondition + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType ELSE IF( @Sort = 1 ) SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @ID + ' >(select max(' + @ID + ') from(' + @SqlSelect + ' top ' + Cast(@pageSize*(@page-2)+@lastcount AS VARCHAR( 20)) + ' ' + @ID + ' from ' + @tblName + ' where (1=1) ' + @strCondition + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TBMaxID)' + ' ' + @strCondition + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType ELSE SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top ' + Cast(@pageSize AS VARCHAR(4)) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @ID + ' <(select min(' + @ID + ') from(' + @SqlSelect + ' top ' + Cast(@pageSize*(@page-2)+@lastcount AS VARCHAR( 20)) + ' ' + @ID + ' from ' + @tblName + ' where (1=1) ' + @strCondition + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TBMaxID)' + ' ' + @strCondition + ' order by ' + @fldSort + ' ' + @strSortType + ') AS TempTB' + ' order by ' + @fldSort + ' ' + @strFSortType END END ------返回查询结果----- EXEC Sp_executesql @strTmp SELECT Datediff(ms, @timediff, Getdate()) AS 耗时 --print @strTmp SET nocount OFF go

执行示例：

exec proc_paged_2part_selectMax 'tb_testTable','ID,userName,userPWD,userEmail',10,100000,'ID',0,null,'ID',0

这种测试只在单机进行，并且没有在实际开发WEB项目中分页测试，测试项也比较单一，所以不够全面系统，但从其效率相比上，我们可以在数据库分页算法上进行有效的控制。