Paging stored procedure Code for large amounts of data in SQL Server

OK, we first create a database: Data_test, and create a table in this database: tb_testtable

Create DATABASE Data_test--Creating databases Data_test

Go

Use Data_test

Go

CREATE TABLE Tb_testtable--Creating tables

(

ID int identity (1,1) primary key,

UserName nvarchar is not NULL,

Userpwd nvarchar is not NULL,

UserEmail nvarchar () null

)

Go

Then we insert 2 million data into the datasheet:

--Inserting data

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

I first wrote five common stored procedures:

1, using select top and select Not in for paging, the specific code is as follows:

CREATE PROCEDURE Proc_paged_with_notin--Using the Select and select Not in

(

@pageIndex int,--page index

@pageSize INT--Number of records per page

)

As

Begin

SET NOCOUNT on;

DECLARE @timediff datetime--time consuming

declare @sql nvarchar (500)

Select @timediff =getdate ()

Set @sql = ' Select Top ' +str (@pageSize) + ' * from tb_testtable where (ID No in (select Top ' +str (@pageSize * @pageIndex) + ' ID fr Om tb_testtable ORDER BY ID ASC)-ORDER by ID '

Execute (@sql)--a string @sql is written because it is not directly connected to the parameter after select top.

Select DateDiff (MS, @timediff, GetDate ()) as time consuming

SET NOCOUNT off;

End

2, using select top and select MAX (column key)

CREATE PROCEDURE Proc_paged_with_selectmax--using the Select top and select Max (column)

(

@pageIndex int,--page index

@pageSize INT--Number of page records

)

As

Begin

SET NOCOUNT on;

DECLARE @timediff datetime

declare @sql nvarchar (500)

Select @timediff =getdate ()

Set @sql = ' Select Top ' +str (@pageSize) + ' * tb_testtable where id> (select Max (ID) from (select Top ' +str (@pageSize *@ PageIndex) + ' ID from tb_testtable-id ' as temptable) ' ORDER by ID '

Execute (@sql)

Select DateDiff (MS, @timediff, GetDate ()) as time consuming

SET NOCOUNT off;

End

3, using select top and intermediate variables--this method is best for someone on the web, so stick it together and test it.

CREATE PROCEDURE Proc_paged_with_midvar--using id> Maximum ID value and intermediate variable

(

@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 the end from tb_testtable the order by ID

Set @sql = ' Select Top ' +str (@pageSize) + ' tb_testtable where id> ' +str (@ID)

Execute (@sql)

Select DateDiff (MS, @timediff, GETDATE ()) as time consuming

SET NOCOUNT off;

End

4, using the Row_number () method for the new method in SQL Server 2005, using Row_number () to index data rows

CREATE PROCEDURE Proc_paged_with_rownumber--take advantage of the Row_number in SQL 2005 ()

(

@pageIndex int,

@pageSize int

)

As

DECLARE @timediff datetime

Begin

SET NOCOUNT on;

Select @timediff =getdate ()

SELECT * FROM (select *,row_number ()-Idrank from tb_testtable) as Idwithrownumber where Idrank> @pageSize * @pageIndex and idrank< @pageSize * (@pageIndex + 1)

Select DateDiff (MS, @timediff, GETDATE ()) as time consuming

SET NOCOUNT off;

End

5, using temporary tables and row_number

CREATE PROCEDURE Proc_cte--using temporary tables and row_number

(

@pageIndex int,--page index

@pageSize INT--Number of page records

)

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 ()/' +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

OK, now that the stored procedure has been created, we are on page 2nd, page 1000th, 10,000th, 100,000th, 199,999th page with 10 data per sheet, and the time consuming unit: MS per page Test 5 times to take its average