Comparison of Three paging Methods

Solution 1: (use the select top paging mode)
Alter procedure [dbo]. [selPagesByTop]
@ TblName nvarchar (255), -- table name
@ PriKeyName nvarchar (50), -- primary key column or label column
@ FldNames nvarchar (1000), -- field name. Multiple fields are separated by commas (,).
@ PageSize int, -- page size
@ PageIndex int, -- page number
@ OrderType nvarchar (200), -- set sorting. '': No sorting requirements. 0: Primary Key ascending order. 1: Primary Key descending string: Custom sorting rule.
@ StrWhere nvarchar (2000), -- Query condition (Note: Do not add where)
@ StrJoin nvarchar (1000) -- connect table
AS
 
Declare @ strByPage nvarchar (4000) -- paging query statement
Declare @ strTotal nvarchar (1000) -- count records that meet the condition
Declare @ strNonResult nvarchar (1500) -- return the statement of null record
Declare @ strTmp nvarchar (100)
Declare @ strOrder nvarchar (200)
Declare @ rowcount int
 
Set nocount on
If @ ordertype =''
Begin
Set @ strorder =''
Set @ strtmp =''
End
Else if @ ordertype = '0' -- descending order
Begin
Set @ strtmp = @ tblname + '.' + @ prikeyname + '> (select max ([' + @ prikeyname + ']) from'
Set @ strorder = 'ORDER BY' + @ tblname + '.' + @ prikeyname + 'asc'
End
Else if @ OrderType = '1' -- descending order
Begin
Set @ strTmp = @ tblName + '.' + @ priKeyName + '<(select min ([' + @ priKeyName + ']) from'
Set @ strOrder = 'ORDER BY' + @ tblName + '.' + @ priKeyName + 'desc'
End
Else -- custom sorting rules
Begin
Set @ strTmp =''
Set @ strOrder = 'ORDER BY' + @ OrderType
End
Set @ strjoin = ''+ @ strjoin +''
Set @ strnonresult = 'select' + @ fldnames + 'from' + @ tblname + @ strjoin + 'where 1 = 2'
 
 
If @ strwhere = ''-- if no additional query conditions exist
Begin
Set @ strtotal = n' select @ rowcount = count (*) from' + @ tblname
 
End
Else
Begin
Set @ strtotal = n' select @ rowcount = count (*) from '+ @ tblname + 'where' + @ strwhere
 
End
 
-- Obtain the number of all records that meet the query Conditions
If @ PageIndex = 1
Begin
Exec sp_executeSql @ strTotal, n' @ rowcount int output', @ rowcount output
If @ rowcount = 0
Begin
Exec sp_executeSql @ strNonResult
Return 0
End
End
Else
Set @ rowcount = 0
 
-- Query. The record set is not empty.
 
-- Get the page number of the record and adjust the page number. The page number starts from 1.
 
If @ PageIndex = 1 -- if it is the first page
Begin
If @ strWhere =''
Set @ strByPage = n' select top' + cast (@ PageSize as varchar) + ''+ @ fldNames + 'from' + @ tblName + @ strJoin + @ strOrder
Else
Set @ strByPage = n' select top' + cast (@ PageSize as varchar) + ''+ @ fldNames + 'from' + @ tblName + @ strJoin + 'where' + @ strWhere + @ strOrder
 
End
Else -- Future page
Begin

If (@ OrderType = '0' or @ OrderType = '1') -- sort by primary key in ascending or descending order
Begin
If @ strWhere =''
Set @ strbypage = n' select top' + Cast (@ pagesize as varchar) + ''+ @ fldnames
+ 'From' + @ tblname
+ @ Strjoin
+ 'Where' + @ strtmp
+ '(Select top' + Cast (@ PageIndex-1) * @ pagesize as varchar) + ''+ @ prikeyname
+ 'From' + @ tblname + @ strorder + ') as tmptbl )'
+ @ Strorder
Else
Set @ strbypage = n' select top' + Cast (@ pagesize as varchar) + ''+ @ fldnames
+ 'From' + @ tblname
+ @ Strjoin
+ 'Where' + @ strtmp
+ '(Select top' + Cast (@ PageIndex-1) * @ pagesize as varchar) + ''+ @ prikeyname
+ 'From' + @ tblName + 'where' + @ strWhere + @ strOrder + ') as tmptbl )'
+ 'And' + @ strWhere
+ @ StrOrder
End
Else -- no sorting rules or custom rules
Begin
If @ strWhere =''
Set @ strByPage = n' select top' + cast (@ PageSize as varchar) + ''+ @ fldnames
+ 'From' + @ tblname
+ @ Strjoin
+ 'Where not exists (select * from'
+ '(Select top' + Cast (@ PageIndex-1) * @ pagesize as varchar) + '* from'
+ @ Tblname + @ strorder + ') as tmptable'
+ 'Where tmptable. '+ @ prikeyname +' = '+ @ tblname +'. '+ @ prikeyname + ')'
+ @ Strorder
Else
Set @ strbypage = n' select top' + Cast (@ pagesize as varchar) + ''+ @ fldnames
+ 'From' + @ tblname
+ @ Strjoin
+ 'Where not exists (select * from'
+ '(Select top' + cast (@ PageIndex-1) * @ PageSize as varchar) + '* from'
+ @ TblName + 'where' + @ strWhere + @ strorder + ') as tmptable'
+ 'Where tmpTable. '+ @ priKeyName +' = '+ @ tblName +'. '+ @ priKeyName + ')'
+ 'And' + @ strWhere
+ @ Strorder
End
End
 
Exec sp_executesql @ strbypage
Return @ rowcount
Set nocount off
 
Solution 2: (using SQL cursor Stored Procedure paging)
Alter procedure [DBO]. [selpagesbycursor]
@ Pageindex int, -- page n
@ Pagesize int, -- number of lines per page
@ Sqlstr nvarchar (4000)
 
As
Set nocount on
 
Declare @ P1 int, -- P1 is the cursor id
@ Rowcount int
Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
-- Select @ rowcount as total number of rows, ceiling (1.0 * @ rowcount/@ pagesize) as page number, @ PageIndex as current page
Set @ PageIndex = (@ PageIndex-1) * @ PageSize + 1
Exec sp_cursorfetch @ P1, 16, @ PageIndex, @ PageSize
Exec sp_cursorclose @ P1
 
 
Return @ rowcount
Set nocount off
 
Solution 3: (paging with common expressions)
ALTER procedure [dbo]. [selTestTable]
@ PageIndex int,
@ PageSize int
 
As
Declare @ rowcount numeric
Declare @ intStart numeric
Set nocount on
Set @ intStart = (@ PageIndex-1) * @ PageSize + 1
If @ intStart = 1
SELECT @ rowcount = count (ID) from TestTable where FirstName like '% aa %'
Else
Set @ rowcount = 0
;
 
WITH PartsCTE AS (Select ROW_NUMBER () OVER (order by ID) as row,
*
From TestTable where FirstName like '% aa %'
)
Select *
From PartsCTE

Where row between @ intStart and @ intStart + @ PageSize-1
Return @ rowcount
Set nocount off
 
Solution 2: The first query is the fastest, because the other two schemes also need to count the total number of records.
Because no statistics are required for the following pages, solution 2 is the slowest.
Solution 3 has the same speed as solution 1, and solution 3 may have some minor advantages.
 
My suggestion is:
Use solution 2 to query for the first time and obtain the total number of records. Use the other two solutions on the following page.