Writing and performance comparison of several SQL Server paging stored procedures

Writing and performance comparison of several SQL Server paging stored procedures
The five paging statements of the stored procedure, the following code is from forgetting when to Ctrl + C from others, so just as a collection, I hope the author will not spray me.
------ Database creation tutorial 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 parameter is directly connected after select top, it is written as a string @ SQL
Select datediff (MS, @ timediff, GetDate () as time consumed
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 consumed
Set nocount off;
End
 

--- 3. Use select top and intermediate variables -- this method is best because some people on the Internet say it is best ---

Create procedure proc_paged_with_Midvar -- use ID> the 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 consumed
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 () 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 consumed
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 (400)
Declare @ strSql nvarchar (400)
Declare @ datediff datetime
Begin
Select @ datediff = GetDate ()
Set @ ctestr = 'with Table_CTE
(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