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 table1Starting 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>5What does this mean? Break it downFirst, slightly modify the sql statement entered by the userAdd 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
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
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!