SQL2005存储过程分页查询

select * from
(select goods_id,ROW_NUMBER() OVER (order by goods_name)
as curr from goods ) as t
where t.curr>=startrow
and t.curr<= endrow

其中satrtrow endrow 分别表示当前页的起始行 终止行
具体可用 startrow = (intpage-1)*perpagesize +1
endrow = intpage*perpagesize


SQL Server 2005 的分页查询(ROW_NUMBER() OVER 语句)
DECLARE @rows_per_page AS INTEGER
DECLARE @current_page AS INTEGER
DECLARE @total_pages AS INTEGER
DECLARE @start_item AS INTEGER
DECLARE @items_count AS INTEGER
-- 设置每页的行数
SET @rows_per_page = 10
-- 设置要显示的页号(从1开始)
SET @current_page = 3
SELECT @total_pages = COUNT(*) / @rows_per_page + 1,
@items_count= COUNT(*)
FROM BarefootIndex;--表名（这是修改的地方）
--计算此页中从第几个开始显示
SET @start_item = @rows_per_page * (@current_page - 1)
select * from
(
select ROW_NUMBER() OVER (order by ID) as item--用什么排序(返回正在显示第几条)
,@items_count AS items_count --一共有多少条
,@current_page AS current_page --当前页
,@total_pages AS total_pages --一共多少页
,* from BarefootIndex--表名（这是修改的地方）
) as T
where T.item >= @start_item + 1
AND T.item <= @start_item + @rows_per_page

-----------------------------------------------------------------------

Sql Server 2005出来已经很久了，一些新的功能应该得到更好的应用，下面是利用2005里面新的函数

ROW_NUMBER() 写的分页存储过程（一个老外写的，我就直接Copy过来了）：

@datasrc
   
   
   
- the table (or stored procedure, etc.) name
@orderBy
   
- the ORDER BY clause
@fieldlis
   
- the fields to return (including calculated expressions)
@filter
   
- the WHERE clause
@pageNum
   
- the page to return (must be greater than or equal to one)
@pageSize
   
- the number of records per page

CREATE PROCEDURE [dbo].[utilPAGE]
@datasrc nvarchar(200)
,@orderBy nvarchar(200)
,@fieldlist nvarchar(200) = '*'
,@filter nvarchar(200) = ''
,@pageNum int = 1
,@pageSize int = NULL
AS
SET NOCOUNT ON
DECLARE
     @STMT nvarchar(max)         -- SQL to execute
    ,@recct int                  -- total # of records (for GridView paging interface)

IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'
IF @pageSize IS NULL BEGIN
    SET @STMT = 'SELECT   ' + @fieldlist +
                 'FROM     ' + @datasrc +
                 'WHERE    ' + @filter +
                 'ORDER BY ' + @orderBy
    EXEC (@STMT)                 -- return requested records
END ELSE BEGIN
    SET @STMT = 'SELECT   @recct = COUNT(*)
                  FROM     ' + @datasrc + '
                  WHERE    ' + @filter
    EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
    SELECT @recct AS recct       -- return the total # of records

    DECLARE
      @lbound int,
      @ubound int

    SET @pageNum = ABS(@pageNum)
    SET @pageSize = ABS(@pageSize)
    IF @pageNum < 1 SET @pageNum = 1
    IF @pageSize < 1 SET @pageSize = 1
    SET @lbound = ((@pageNum - 1) * @pageSize)
    SET @ubound = @lbound + @pageSize + 1
    IF @lbound >= @recct BEGIN
      SET @ubound = @recct + 1
      SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if         

                                     -- no records would be on the
                                              -- specified page
    END
    SET @STMT = 'SELECT ' + @fieldlist + '
                  FROM    (
                            SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
                            FROM    ' + @datasrc + '
                            WHERE   ' + @filter + '
                          ) AS tbl
                  WHERE
                          row > ' + CONVERT(varchar(9), @lbound) + ' AND
                          row < ' + CONVERT(varchar(9), @ubound)
    EXEC (@STMT)                 -- return requested records
END


-------------------------------------------------------------

我们以前在开发大数据量的分页存储过程时，往往都为了怎么样实现高效的性能，而大伤脑筋，似乎总

是想写出最佳的存储过程分页方法，我们假如建立一个学生基本信息表StudentInfo，我们看在Sql

Server 2000中我们实现的存储过程
CREATE PROCEDURE p_GetStudentInfo
@strWhere   varchar(1500)   -- 查询条件 (注意: 不要加 where)
,@PageSize    int = 50          -- 页尺寸
,@PageIndex   int = 1           -- 页码
AS

BEGIN

declare @strSQL   varchar(5000)       -- 主语句
declare @strTmp   varchar(110)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型

set @strTmp = '<(select min'
set @strOrder = ' order by studentinfoid desc'

if @PageIndex = 1
begin
     if @strWhere != ''
     set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo where ' + @strWhere

+ ' ' + @strOrder
      else
      set @strSQL = 'select top ' + str(@PageSize) +' *   from studentinfo '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo
where studentinfoid' + @strTmp + '(studentinfoid) from (select top ' + str((@PageIndex-1)

*@PageSize) + ' studentinfoid from studentinfo' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
     set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo
      where studentinfoid' + @strTmp + '(studentinfoid) from
    (select top ' + str((@PageIndex-1)*@PageSize) + ' studentinfoid from studentinfo where '

+ @strWhere + ' '
         + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
--print (@strSQL)
exec (@strSQL)

END

GO

我们在上面的存储过程中可以看到，如果执行的是第一页的记录时，我们可以看到只执行

'select top ' + str(@PageSize) +' * from studentinfo where ' + @strWhere + ' ' +

@strOrder
就可以了，而如果翻页的时候，就会在条件查询中又嵌套子查询，势必性能会有所下降，而这个已经

在我们Sql Server2000中算是高效的分页存储过程实现方式了，而现在如果我们换作Sql Server 2005的

时候，我们是不是还是用这种方式呢?

在Sql Server 2005中，我们可以利用新增函数row_number()来更高效的实现分页存储
CREATE PROCEDURE p_GetStudentInfo
@PageSize INT,
@PageIndex INT,
@strWhere varchar(1500) -- 查询条件(注意: 不要加where)
As
Begin
select * from (
select row_number() over (order by StudentInfoId) row,* from StudentInfo ) StudentInfo
where row between @PageSize*(@PageIndex-1) and @PageSize*@PageIndex
End
go
依照群组显示每条记录在该群组中出现的顺序位置，在显示每条记录编号时非常有用，并且搭配OVER子句

，这样就可以实现查询记录的条数了。

我们现在来比较一下它们执行的性能，StudentInfo表中现在有30多万条数据，我们分别来执行下面

的存储过程：
Exec p_GetStudentInfo
@PageSize = 10000,
@PageIndex = 10,
@strWhere = '1=1'
在Sql Server 2000中，执行的时间是接近2秒；

在Sql Server 2005中，执行的时间显示的是接近0秒。

如果数据涉及到千万级的数据时,比较上面两种方法,就可以看到一些显著的差别了。简单的样子。
---------------------------------------------------------

SQL2005增加了不少新特性，其中NTILE和ROW_NUMER使得我们不再为SQL如何灵活方便的分页伤脑筋了（不

必再羡慕Oracle等数据库了）。
下面就是一个很简单的分页查询语句： DECLARE @rows_per_page AS INTEGER
DECLARE @current_page AS INTEGER
DECLARE @total_pages AS INTEGER

-- 设置每页的行数
SET @rows_per_page = 20
-- 设置要显示的页号(从1开始)
SET @current_page = 2
-- 计算总页数
SELECT @total_pages = COUNT(*) / @rows_per_page
FROM testtable;

-- 列出指定页的内容
WITH t AS
(
SELECT NTILE(@total_pages) OVER(ORDER BY id) AS page_number, *
FROM testtable
)
SELECT * from t
WHERE page_number = @current_page 　　程序简单到可以不用说明的程度。
我们可以利用上述简单的语句，变化排序条件和查询表，就可以做出一个很通用的分页查询的存储过

程或查询语句了。

同样的，使用ROW_NUMBER也可以做到分页查询： DECLARE @rows_per_page AS INTEGER
DECLARE @current_page AS INTEGER
DECLARE @start_row_num AS INTEGER

-- 设置每页的行数
SET @rows_per_page = 20
-- 设置要显示的页号(从1开始)
SET @current_page = 2
-- 设置开始行号
SET @start_row_num = (@current_page - 1) * @rows_per_page

WITH t AS
(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_number, *
FROM testtable
)
SELECT * from t
WHERE row_number BETWEEN @start_row_num AND @start_row_num + @rows_per_page　　
似乎更简单的样子。