SQL Server paging query processing method summary, SQL Server Paging

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!