SQL Server 存储过程分页大全(2005,2000)-摩杜云开发者社区

-----------------------------------------------------------------------------------
--作者：启程 http://www.letwego.cn/
--只是本人大概测试，不一定完全正确
DECLARE @DateBegin datetime
DECLARE @DateEnd datetime
SET @DateBegin=getdate()
--------------------此处的分页存储过程均不是通用的，通用的都要拼接Sql语句------------------
-----测试数据：Cpu：Core 1.8，内存：1G， 1百万条，取第 5000 页，每页 100条，结果时间毫秒

--NOT IN (需要主键，需要拼接Sql，速度第二)
--EXEC spPage2000 @pageSize=100,@pageIndex=5000,@Counts=0
--测试:3080 3016 3236 3173 3186 3233 3203 3123 3216 3060

--三次 ORDER BY (不需要主键，需要拼接Sql，速度第五)
--EXEC spPage2001 @pageSize=100,@pageIndex=5000,@Counts=0
--测试:42890 52453 48220

--临时表 (需要主键，不需要拼接Sql，速度第三)
--EXEC spPage2002 @pageSize=100,@pageIndex=5000,@Counts=0
--测试:13890 13656 14000

--游标 (不需要主键，不需要拼接Sql,最容易做成通用，速度第四)
--EXEC spPage2003 @pageSize=100,@pageIndex=5000,@Counts=0
--测试:20453 21216 21346

--SQL 2005 ROW_NUMBER(不需要主键[有主键更快]，不需要拼接Sql，速度第一)
EXEC spPage2005 @pageSize=100,@pageIndex=5000,@Counts=0
--有主键测试:830 873 830 890 843 826 830
--无主键测试:15890 14970 15703
---------------------------------------------------------------------

SET @DateEnd=getdate() 
SELECT DATEDIFF(millisecond,@DateBegin,@DateEnd)

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

--表结构 
CREATE TABLE [dbo].[UserInfo2]( 
    [id] [int] IDENTITY(1,1) NOT NULL, 
    [user] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, 
    [password] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, 
    [datetime] [datetime] NULL  DEFAULT (getdate()), 
    [content] [ntext] COLLATE Chinese_PRC_CI_AS NULL 
)

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

--插入数据 
--SELECT COUNT(1) FROM userinfo 
DECLARE @I int 
SET @I=1 WHILE @I<=100 BEGIN --需要测试时间性能请修改此处 
    INSERT INTO userinfo ( 
        [user], 
        password, 
        [datetime], 
        [content] 
    )     VALUES ( 
        'user ' + CAST(@I AS nvarchar(50)), 
        'password ' + CAST(@I AS nvarchar(50)), 
        GETDATE(), 
        '添加系统用户组添加系统用户组添加系统用户组添加系统用户组添加系统用户组添加系统用户组' 
    )     SET @I = @I + 1 
END

---------------------------此处的分页存储过程均不是通用的，通用的都要拼接Sql语句----------------------------

-- Description:利用Sql2005 ROW_NUMBER，分页存储过程(不需要主键，不需要拼接Sql) 
CREATE PROCEDURE [dbo].[spPage2005] 
( 
@pageSize int = 20,    ----每页显示的记录个数 
@pageIndex int = 1,    ----要显示那一页的记录 
@Counts int = 0 OUTPUT      ----查询到的记录数 
) 
AS 
SET NOCOUNT ON DECLARE @pageUp INT  
DECLARE @pageDown INT --获得总记录数 
SELECT @Counts = COUNT(1) FROM UserInfo --当前页的第一条记录RowID 
SET @pageDown = @pageSize * (@pageIndex - 1) + 1 --当前页的最后一条记录RowID 
SET @pageUp = @pageSize * @pageIndex --获取分页后的数据 
SELECT T.* --没有主键此处不用 T. 
FROM  
( 
SELECT id ,--没有主键此处改用 * 
   RowID = ROW_NUMBER () OVER (ORDER BY id DESC) 
FROM UserInfo 
) AS PageTableList 
JOIN UserInfo AS T ON T.id = PageTableList.id --没有主键此句删除 
WHERE RowID BETWEEN @pageDown AND @pageUp 
ORDER BY T.id DESC --没有主键此句删除

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

-- Description:Sql2000，分页存储过程 NOT IN(需要主键，需要拼接Sql) 
CREATE PROCEDURE dbo.spPage2000 
( 
    @pageSize int = 20,            ----每页显示的记录个数 
    @pageIndex int = 1,            ----要显示那一页的记录 
    @Counts int = 0 OUTPUT      ----查询到的记录数 
) 
AS 
SET NOCOUNT ON DECLARE @pageUp INT  
DECLARE @SQL nvarchar(4000)  --获得总记录数 
SELECT @Counts = COUNT(1) FROM UserInfo SET @pageUp = @pageSize * (@pageIndex - 1) 
--获取分页后的数据 
SET @SQL= 
'SELECT TOP ' + CAST(@pageSize AS varchar(20)) + 
' * FROM UserInfo ' + 
' WHERE id NOT IN '+ 
'('+ 
'SELECT TOP ' + CAST(@pageUp AS varchar(20)) + ' id ' + 
' FROM UserInfo ORDER BY id DESC ' + 
') ORDER BY id DESC ' EXEC(@SQL)

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

-- Description:Sql2000，分页存储过程 三次 ORDER BY(不需要主键，需要拼接Sql) 
CREATE PROCEDURE dbo.spPage2001 
( 
    @pageSize int = 20,            ----每页显示的记录个数 
    @pageIndex int = 1,            ----要显示那一页的记录 
    @Counts int = 0 OUTPUT      ----查询到的记录数 
) 
AS 
SET NOCOUNT ON DECLARE @pageUp INT  
DECLARE @SQL nvarchar(4000)  --获得总记录数 
SELECT @Counts = COUNT(1) FROM UserInfo SET @pageUp = @pageSize * (@pageIndex + 1) 
--获取分页后的数据 
SET @SQL= 
('SELECT * FROM 
        (SELECT TOP ' + CAST(@pageSize AS varchar(20)) + ' * FROM ' + 
            '(SELECT TOP ' + CAST(@pageUp AS varchar(20)) + ' * FROM UserInfo   ORDER BY id DESC) TB2 '+ 
            ' ORDER BY id ASC ) TB3 '+ 
              ' ORDER BY id DESC  ') EXEC(@SQL)

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

-- Description:Sql2000，分页存储过程 临时表(需要主键，不需要拼接Sql) 
CREATE PROCEDURE dbo.spPage2002 
( 
    @pageSize int = 20,            ----每页显示的记录个数 
    @pageIndex int = 1,            ----要显示那一页的记录 
    @Counts int = 0 OUTPUT      ----查询到的记录数 
) 
AS 
SET NOCOUNT ON DECLARE @pageUp INT  
DECLARE @pageDown INT  --获得总记录数 
SELECT @Counts = COUNT(1) FROM UserInfo --当前页的第一条记录RowID 
SET @pageDown = @pageSize * (@pageIndex - 1) + 1 --当前页的最后一条记录RowID 
SET @pageUp = @pageSize * @pageIndex 
--定义插入临时表的总数据 
SET rowcount @pageUp --定义临时表变量 
DECLARE @indextable table(indexID int identity(1,1),nid int) --插入到临时表 
INSERT INTO @indextable(nid) SELECT ID FROM UserInfo ORDER BY ID DESC 
--获取分页后的数据 
SELECT * FROM UserInfo p,@indextable t WHERE p.ID=t.nid 
AND t.indexID>=@pageDown and t.indexID<=@pageUp ORDER BY t.indexID

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

-- Description:Sql2000，分页存储过程 游标(不需要主键，不需要拼接Sql) 
--此存储过程 出来多一个空的结果集 ，暂时不明白 
CREATE PROCEDURE dbo.spPage2003  
(   
    @sql nvarchar(4000) = NULL, --要执行的sql语句 
    @pageSize int = 20,    ----每页显示的记录个数 
    @pageIndex int = 1,    ----要显示那一页的记录 
    @Counts int = 0 OUTPUT      ----查询到的记录数 
) 
AS 
SET NOCOUNT ON DECLARE @p1 int--P1是游标的id  
SET @sql = 'SELECT * FROM UserInfo Order By Id Desc' 
EXEC sp_cursoropen @p1 OUTPUT,@sql,@scrollopt=1,@ccopt=1,@rowcount=@Counts OUTPUT SELECT @Counts=ceiling(1.0*@Counts/@pageSize) ,@pageIndex=(@pageIndex-1)*@pageSize+1 
--SELECT @pageIndex 
EXEC sp_cursorfetch @p1,16,@pageIndex,@pageSize 
--EXEC sp_cursorclose @p1