SQL Server paging query processing method summary, SQL Server Paging
Sqlserver2008 does not support the keyword limit, so its paging SQL query statement cannot be performed using MySQL. Fortunately, sqlserver2008 provides top, rownumber, and other keywords, in this way, you can use these keywords to implement paging.
Below are some query scripts I have found on the Internet:
Several efficient paging SQL query statements of sqlserver2008
Top solution:
SQL code:
Select top 10 * from table1where id not in (select top start position id from table1)
Max:
SQL code:
Select top 10 * from table1where 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) ttwhere temprownumber> Start position
Three paging Methods: max solution, top solution, and row Solution
Efficiency:
1st: row
2nd: max
3rd: top
Disadvantages:
Max: You must write complex SQL statements and do not support sorting of non-unique columns.
Top: complex SQL statements must be compiled, and compound primary keys are not supported.
Row: sqlserver2000 is not supported.
Test data:
A total of 3.2 million pieces of data are displayed, and 10 pieces of data are displayed on each page. 20 thousand pages, 0.15 million pages, and 0.32 million pages are tested respectively.
Page number, top scheme, max scheme, and row Scheme
20 thousand, 60 ms, 46 ms, 33 ms
0.15 million, 453 ms, 343 ms, 310 ms
0.32 million, 953 ms, 720 ms, 686 ms
It is a paging solution that concatenates SQL statements through programs,
You do not need to write complicated SQL logic for the SQL statements you have mentioned.
The following SQL statements are provided by normal users:
SQL code
select * from table1
Start from 5th, query 5, and change the SQL statement
SQL code
select *from (select row_number()over(order by tempcolumn)temprownumber,*from (select top 10 tempcolumn=0,* from table1)t)ttwhere temprownumber>5
What does this mean? Break down
First, modify the SQL statement you entered
Add the top start position + number after select
Add a column of tempcolum.
SQL code
select top 20 tempcolumn=0,* from clazz
Nested layer to query travel numbers
The column is used here for order.
(I do not know why the SQL Server row_number function must be order)
SQL code
Select row_number () over (order by tempcolumn) temprownumber, * from (modified query) t
Set another layer to filter out rows with a row number smaller than the start position.
SQL code
Select * from (second layer) ttwhere temprownumber> 10
Summary
The above is a summary of the SQL Server paging query processing method introduced by xiaobian. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!