SQL SERVER 2005 分页存储过程

-- =============================================
-- Author: <Anncesky>
-- Create date: <2009/3/16>
-- Description: <Paged>
-- =============================================
ALTERPROCEDURE[dbo].[usp_pagination]
@tableNamevarchar(255), -- 表名
@colNamevarchar(255)='*', -- 字段名串
@pageSizeint=10, -- 页尺寸
@currentPageint=1, -- 页码
@orderStrvarchar(255) ='',-- 排序类型字段串（不带order by,带asc,desc,必须的）
@whereStrvarchar(255) ='',-- 查询条件 （注意: 不要加 where）
@recordCountint output
AS
BEGIN
SET NOCOUNT ON;
declare@sqlnvarchar(4000)
declare@TotalPagesint 

--计算总记录数及总页数 
set@sql='select @recordCount = count(*) from '+@tableName+' where 1=1 '+@whereStr
exec sp_executesql @sql,N'@recordCount int output',@recordCount output
select@TotalPages=CEILING((@recordCount+0.0)/@PageSize)

--处理页数超出范围情况
if@currentPage<=0 
set@currentPage=1
if@currentPage>@TotalPages
set@currentPage=@TotalPages
set@sql='select '+@colName+' from (select top(@currentPage*@pageSize) '+@colName+',row_number() over(order by '+@orderStr+') as rowNumber from '+@tableName+' where 1=1 '+@whereStr+') t where t.rowNumber >= ((@currentPage-1)*@pageSize+1)'

--print @Sql 
exec sp_executesql @sql,N'@currentPage int, @pageSize int',@currentPage,@pageSize 
END

 

以上由于使用 top 形式做为分页,性能太差,以下为优化版

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





-- =============================================
-- Author:        <Anncesky>
-- Create date: <2010/3/16>
-- Description:    <Paged>
-- =============================================
ALTER PROCEDURE [dbo].[usp_pagination]
    @tableName    varchar(255),       -- 表名
    @colName      varchar(1000)='*',   -- 字段名串
    @pageSize     int = 10,           -- 页尺寸
    @currentPage  int = 1,            -- 页码
    @orderStr     varchar(255) = '',-- 排序类型字段串（不带order by,带asc,desc,必须的）
    @whereStr     varchar(5000) = '',-- 查询条件 （注意: 不要加 where）
    @recordCount  int output
AS
BEGIN
    SET NOCOUNT ON;
    declare @sql nvarchar(4000)
    declare @TotalPages int   

    --计算总记录数及总页数     
    set @sql = 'select @recordCount = count(*) from ' + @tableName + ' where 1=1 ' + @whereStr
    exec sp_executesql @sql,N'@recordCount int output',@recordCount output
    select @TotalPages=CEILING((@recordCount+0.0)/@PageSize)

    --处理页数超出范围情况
    if @currentPage<=0 
        set @currentPage = 1
    if @currentPage>@TotalPages
        set @currentPage = @TotalPages
    set @sql = 'select '+ @colName + ' from (select ' + @colName + ',row_number() over(order by ' + @orderStr + ') as rowNumber from ' + @tableName + ' where 1=1 ' + @whereStr + ') t where t.rowNumber BETWEEN ((@currentPage-1)*@pageSize+1) AND @currentPage * @pageSize'
    
    print @Sql   
    exec sp_executesql @sql,N'@currentPage int, @pageSize int',@currentPage,@pageSize  
END

--

新版遇到大数据量时,性能明显增强,其实还可以做些优化,比如把where 做判断,把 1=1 这个去掉,还有查询第一页可以直接使用top句子
-- 这样应该还可以更快些,不过本人太懒
-- 除了使用 row_number() over 关键字,还可以使用 Set Rowcount,Set Rowcount有点类似游标,传中说使用Set Rowcount分页性能更强
-- 不过在SQL 2005中,Set Rowcount直接影响/UPDATE/INSERT/DELETE,如果遇到并发数高的应用,可能会有问题,不过在SQL 2008中,好像去掉了影响