Discussion on five methods and performance comparison _mssql of SQL Server paging stored procedure

In SQL Server database operations, we often use stored procedures to implement the query data paging to facilitate the browsing of the viewer.

To create a database data_test:

Create DATABASE data_test go use  
data_test go create table  
tb_testtable  --Creating Tables  
(  
ID int Identity (1,1) primary key,  
userName nvarchar not null,  
userpwd nvarchar (.) not NULL,  
UserEmail nvarchar () null  
)  

Insert 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  

1. Pagination using select top and select Not in

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  
select @timediff =getdate ()  
Set @sql = ' Select Top ' +str (@pageSize) + ' * tb_testtable where (id not in (select Top ' +str (@pageSize * @pageIndex) + ' ID from tb_testtable o Rder by ID ASC)-  
-The ORDER by ID ' execute (@sql)--is not directly connected to the parameter because of select top, so it is written as a string @sql  
Select DateDiff (MS, @timediff, GetDate ()) as time-consuming  
set NOCOUNT off;  

2. Use 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  --page record number  
)  
as  
begin  
set NOCOUNT on;  
DECLARE @timediff datetime  
declare @sql nvarchar (  
) Select @timediff =getdate ()  
Set @sql = ' Select Top ' +str (@pageSize) + ' * tb_testtable where (id> (select Max (ID) from (select Top ' +str (@pageSize * @pageIndex) + ' ID from T B_testtable order by ID) as temptable)-Order by ID '  
execute (@sql)  
Select DateDiff (MS, @timediff, GetDate ()) as consumption When  
set NOCOUNT off;  
End 

3. Use select top and intermediate variables

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 (+)  
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 to tb_testtable order by ID  
Set @sql = "SELECT Top" +str (@pageSize) + ' * from Tb_testta ble where id> ' +str (@ID)  
execute (@sql)  
Select DateDiff (MS, @timediff, GETDATE ()) as time-consuming  
set NOCOUNT off ;  

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

CREATE PROCEDURE Proc_paged_with_rownumber--using 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 () 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 time-consuming  
set NOCOUNT off;  
End

5. Use of temporary tables and row_number

CREATE PROCEDURE Proc_cte--using temporary tables and Row_number  
(  
@pageIndex int,--page index  
@pageSize int  --page record number  
)  
As  
set NOCOUNT on;  
declare @ctestr nvarchar ()  
declare @strSql nvarchar ()  
declare @datediff datetime  
begin  
SELECT @ The Datediff=getdate ()  
Set @ctestr = ' with Table_cte as  
(select Ceiling (Row_number () over (the 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

Of the five methods mentioned above, the third method that utilizes select top and intermediate variables is the most efficient. All about the five methods of SQL Server paging stored procedures and performance comparisons The end of this, I hope to help you.