SQL Server 大量数据的分页存储过程代码

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v6.0.70
 * Time: 2014/7/18 17:00:57
 ************************************************************/

--SQL Server 大量数据的分页存储过程代码

CREATE DATABASE data_Test --创建数据库data_Test 
GO   
USE data_Test 
GO   
IF OBJECT_ID('tb_TestTable') IS NOT NULL
    DROP TABLE tb_TestTable
GO 
CREATE TABLE tb_TestTable --创建表
(
	id         INT IDENTITY(1, 1) PRIMARY KEY,
	userName   NVARCHAR(20) NOT NULL,
	userPWD    NVARCHAR(20) NOT NULL,
	userEmail  NVARCHAR(40) NULL
) 
GO   
--然后我们在数据表中插入2000000条数据: 

--插入数据 
SET IDENTITY_INSERT tb_TestTable ON 
DECLARE @count INT 
SET @count = 1 
WHILE @count <= 2000000
BEGIN
    INSERT INTO tb_TestTable
      (
        id,
        userName,
        userPWD,
        userEmail
      )
    VALUES
      (
        @count,
        'admin',
        'admin888',
        'lli0077@yahoo.com.cn'
      ) 
    SET @count = @count + 1
END 
SET IDENTITY_INSERT tb_TestTable OFF 
--常用存储过程： 

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

IF OBJECT_ID('proc_paged_with_notin') IS NOT NULL
BEGIN
    PRINT 'Dropping procedure proc_paged_with_notin'
    DROP PROCEDURE [proc_paged_with_notin]  
    IF @@ERROR = 0
        PRINT 'Procedure proc_paged_with_notin dropped'
END
GO  
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
GO
IF @@ERROR = 0
    PRINT 'Procedure proc_paged_with_notin created'
GO 
--2,利用select top 和 select max(列键) 
 
GO 

IF OBJECT_ID('proc_paged_with_selectMax') IS NOT NULL
BEGIN
    PRINT 'Dropping procedure proc_paged_with_selectMax'
    DROP PROCEDURE [proc_paged_with_selectMax]  
    IF @@ERROR = 0
        PRINT 'Procedure proc_paged_with_selectMax dropped'
END
GO 
 
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
GO

IF @@ERROR = 0
    PRINT 'Procedure proc_paged_with_selectMax created'
GO 

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

IF OBJECT_ID('proc_paged_with_Midvar') IS NOT NULL
BEGIN
    PRINT 'Dropping procedure proc_paged_with_Midvar'
    DROP PROCEDURE [proc_paged_with_Midvar]  
    IF @@ERROR = 0
        PRINT 'Procedure proc_paged_with_Midvar dropped'
END
GO 
 
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
GO

IF @@ERROR = 0
    PRINT 'Procedure proc_paged_with_Midvar created'
GO 

	--4,利用Row_number() 此方法为SQL server 2005中新的方法,利用Row_number()给数据行加上索引 
	 
GO 
	
IF OBJECT_ID('proc_paged_with_Rownumber') IS NOT NULL
BEGIN
    PRINT 'Dropping procedure proc_paged_with_Rownumber'
    DROP PROCEDURE [proc_paged_with_Rownumber]  
    IF @@ERROR = 0
        PRINT 'Procedure proc_paged_with_Rownumber dropped'
END
GO 
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
GO
	
IF @@ERROR = 0
    PRINT 'Procedure proc_paged_with_Rownumber created'
GO 
	
	
		--5，利用临时表及Row_number 
		 
GO 
		
IF OBJECT_ID('proc_CTE') IS NOT NULL
BEGIN
    PRINT 'Dropping procedure proc_CTE'
    DROP PROCEDURE [proc_CTE]  
    IF @@ERROR = 0
        PRINT 'Procedure proc_CTE dropped'
END
GO 
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
GO
		
IF @@ERROR = 0
    PRINT 'Procedure proc_CTE created'
GO