Five paging stored procedures based on SQL Server and Performance Comparison

Five paging stored procedures based on SQL Server and Performance Comparison

In SQL Server database operations, we often use stored procedures to implement paging processing of the queried data to facilitate browsing by viewers.

Create a database data_Test:

Create database data_Test GO use data_Test GO create table tb_TestTable -- create a table (id int identity (1, 1) primary key, userName nvarchar (20) not null, userPWD nvarchar (20) not null, userEmail nvarchar (40) 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. Use select top and select not in for paging

The Code is as follows:

Create procedure proc_paged_with_notin -- use select top and select not in (@ pageIndex int, -- page index @ pageSize int -- number of records per page) as begin set nocount on; declare @ timediff datetime -- time consumed 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) -- because the select top is not directly connected to the parameter, it is written as a string @ SQL select datediff (MS, @ timediff, GetDate ()) set nocount off; end

2. Use select top and select max (column key)

Create procedure proc_paged_with_selectMax -- use 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) + '* 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 time-consuming set nocount off; end

3. Use select top and intermediate Variables

Create procedure proc_paged_with_Midvar -- use the ID> maximum ID value and the 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 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 time-consuming set nocount off; end

4. Use Row_number () to add an index to the data row in SQL server 2005.

Create procedure proc_paged_with_Rownumber -- use Row_number () (@ pageIndex int, @ pageSize int) in SQL 2005 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 temporary tables and Row_number

Create procedure proc_CTE -- use temporary tables and Row_number (@ pageIndex int, -- page index @ pageSize int -- number of page records) 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 (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

Among the above five methods, the third method using select top and intermediate variables is the most efficient. This is the end of all the five methods for SQL Server paging stored procedures and performance comparison. I hope this will help you.