Methods and performance comparisons of five SQL Server paging stored procedures _mssql

In SQL Server database operations, we often use stored procedures to implement the query data paging to facilitate the browsing of the viewer. In this article, we summarize the methods of five SQL Server paging stored procedures and compare their performance, so let's take a look at this process together.

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  
 
) Go  
 
 

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  
 
set Identity_insert tb_testtable off 

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;  
 
End 

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 ;  
 
End 

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 @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.

About the SQL Server database paging stored procedures of five methods and performance comparison of knowledge is introduced here, I hope to help you learn.