SQL Server Paging Query summary

SQLSERVER2008 does not support the keyword limit, so its paged SQL query statements will not be in the way of MySQL, fortunately sqlserver2008 provides the top,rownumber and other keywords, so that can be achieved through the several key words pagination.

Here are some of the types of query scripts I have read online:

Several sqlserver2008 efficient paging SQL query statements
Top solutions:
SQL code:

Select Top Ten * from table1
where ID not in (position ID starting with select top from table1)

Max
SQL code:

Select Top Ten * from table1
Where id> (select Max (ID)
From (select top start position ID from table1 ORDER by ID) TT)

Row
SQL code:

SELECT *
From (
Select Row_number () over (order by Tempcolumn) temprownumber,*
From (select top start position +10 tempcolumn=0,* from table1) t
) TT
where temprownumber> start position
3 Paging methods, max scheme, top scheme, row scheme

Efficiency:
Section 1:row
Section 2:max
Section 3:top

Disadvantages:
Max: Users must write complex SQL and do not support non-unique column sorting
Top: You must write complex SQL and do not support composite primary keys
Row: sqlserver2000 not supported

Test data:
A total of 3.2 million data, each page shows 10 data, respectively, 20,000 pages, 150,000 pages and 320,000 pages were tested.

Page number, top scheme, max scheme, row scheme
20,000, 60MS,46MS,33MS
150,000, 453ms,343ms,310ms
320,000, 953ms,720ms,686ms

is a paging scheme that uses a program to stitch SQL statements.
User-mentioned SQL statements do not need to write complex SQL logic

The user provides SQL as follows
SQL code
SELECT * FROM table1

Starting with 5th, query 5, and after processing, SQL becomes
SQL code
SELECT *
From (
Select Row_number () over (order by Tempcolumn) temprownumber,*
From (select Top ten tempcolumn=0,* from table1) t
) TT
where temprownumber>5

What does that mean? Break it down.

First, the user-entered SQL statements are modified slightly
add top start position + bar number after select to
plus a column of tempcolum.
sql code

Nesting a layer so that you can query the travel number
That column was used here for order by.
(I don't know why SQL Server's Row_number function must have an ORDER by)
SQL code
Select Row_number () over (order by Tempcolumn) temprownumber,*
From (modified query) t

Another layer, filter out lines that are less than the starting position
SQL code
SELECT * FROM (second tier) TT
where temprownumber>10

SQL Server Paging Query summary