Explain the paging query processing method of sqlserver

The following are several ways to write query scripts that I have found online, and I hereby share them on the Script House platform for your reference.

sqlserver2008 does not support the keyword limit, so its paging sql query statements will not be able to Use MySQL method. Fortunately, sqlserver2008 provides keywords such as top and rownumber, so that paging can be achieved through these keywords.

The following are the writing methods of several query scripts that I found on the Internet:

Several sqlserver2008 efficient paging sql query statements

top solution:

sql code:

select top 10 * from table1
where id not in(select top 开始的位置 id from table1)

max :

sql code：

select top 10 * from table1
where id>(select max(id)
from (select top 开始位置 id from table1 order by id)tt)

row:

sql code:

select *
from (
select row_number()over(order by tempcolumn)temprownumber,*
from (select top 开始位置+10 tempcolumn=0,* from table1)t
)tt
where temprownumber>开始位置

3 paging methods, namely max plan, top plan, row plan

Efficiency:

1st: row

2nd: max

3rd: top

Disadvantages:

max: The user must write complex sql, does not support non-unique column sorting

top: The user must write complex sql sql, composite primary key is not supported

row: sqlserver2000 is not supported

Test data:

A total of 3.2 million pieces of data, Each page displays 10 pieces of data, and 20,000 pages, 150,000 pages and 320,000 pages were tested respectively.

Page number, top plan, max plan, row plan

20,000, 60ms, 46ms, 33ms
150,000, 453ms, 343ms, 310ms
320,000, 953ms, 720ms , 686ms

is a paging solution that splices sql statements through a program.

The sql statements mentioned by users do not require writing complex sql logic

No user provides sql as follows

sql code

##

select * from table1

Starting from the 5th item, query 5 items, and the sql becomes

sql code after processing

select *
from (
select row_number()over(order by tempcolumn)temprownumber,*
from (select top 10 tempcolumn=0,* from table1)t
)tt
where temprownumber>5

What does this mean? Break it down

First, slightly modify the sql statement entered by the user

Add top after select, the starting position + the number of items become

, and then add a column of tempcolum, it becomes like this

sql code

##

select top 20 tempcolumn=0,* from clazz

Nested one level, so that the row number can be queried

The column just now is used Order by is used here

(I don’t know why sqlserver’s row_number function must order by)

sql code

select row_number()over(order by tempcolumn)temprownumber,*
from (修改过的查询)t

Apply another layer and filter out rows with row numbers smaller than the starting position

sql code

select * from (第二层)tt
where temprownumber>10

The above is the detailed content of Explain the paging query processing method of sqlserver. For more information, please follow other related articles on the PHP Chinese website!