sqlserver2005高效分页

墨蓝 2022-01-10 05:49 323阅读 0赞

Select \* FROM (  
select ROW\_NUMBER()Over(order by cf\_id desc) as rowId,\* from T\_Test  
) as mytable  
where rowId between 21 and 40  
  
  
每页20条，第２页  
  
ROW\_NUMBER是SqlServer2005中的新特性  
  
以下是利用本方法和传统利用Set RowCount方法存储过程的效率比较结果:  
  
未建索引情况下:  
500w数据: 存储过程1分20秒 , 新方法1分18秒  
400w数据：存储过程47秒，新方法41秒  
300w数据：存储过程35秒，新方法30秒  
200w数据：存储过程25秒，新方法20秒  
100W数据：存储过程12秒，新方法10秒  
50W数据：存储过程 7秒，新方法 0秒

=============================================  
\-- Author: <宋辉>  
\-- Create date: <2008-11-14>  
\-- Description: <高效分页存储过程，仅适用于Sql2005>  
\-- Notes: <排序字段强烈建议建索引>  
\-- =============================================  
Alter Procedure \[dbo\].\[up\_Page2005\]  
@TableName varchar(50), --表名  
@Fields varchar(5000) = '\*', --字段名(全部字段为\*)  
@OrderField varchar(5000), --排序字段(必须!支持多字段)  
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)  
@pageSize int, --每页多少条记录  
@pageIndex int = 1 , --指定当前为第几页  
@TotalPage int output --返回总页数  
as  
begin  
  
Begin Tran --开始事务  
  
Declare @sql nvarchar(4000);  
Declare @totalRecord int;  
  
\--计算总记录数  
  
if (@SqlWhere='' or @sqlWhere=NULL)  
set @sql = 'select @totalRecord = count(\*) from ' + @TableName  
else  
set @sql = 'select @totalRecord = count(\*) from ' + @TableName + ' where ' + @sqlWhere  
  
EXEC sp\_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数  
  
\--计算总页数  
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)  
  
if (@SqlWhere='' or @sqlWhere=NULL)  
set @sql = 'Select \* FROM (select ROW\_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName  
else  
set @sql = 'Select \* FROM (select ROW\_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere  
  
  
\--处理页数超出范围情况  
if @PageIndex<=0  
Set @pageIndex = 1  
  
if @pageIndex>@TotalPage  
Set @pageIndex = @TotalPage  
  
\--处理开始点和结束点  
Declare @StartRecord int  
Declare @EndRecord int  
  
set @StartRecord = (@pageIndex-1)\*@PageSize + 1  
set @EndRecord = @StartRecord + @pageSize - 1  
  
\--继续合成sql语句  
set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)  
  
Exec(@Sql)  
\---------------------------------------------------  
If @@Error <> 0  
Begin  
RollBack Tran  
Return -1  
End  
Else  
Begin  
Commit Tran  
Return @totalRecord ---返回记录总数  
End  
end

转载于:https://www.cnblogs.com/dfsxh/archive/2008/11/15/1334019.html