Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to deal with paging queries in sqlserver. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Top scenario:

Sql code:

Select top 10 * from table1where id not in (id from table1 where the select top starts)

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

There are three paging methods: max scheme, top scheme and row scheme.

Efficiency:

1: row

No. 2: max

No. 3: top

Disadvantages:

Max: complex sql must be written by the user, and non-unique column sorting is not supported

Top: complex sql must be written by users, and compound primary keys are not supported.

Row: sqlserver2000 is not supported

Test data:

A total of 3.2 million pieces of data were displayed on each page, and 20, 000, 150000 and 320000 pages were tested respectively.

Page number, top scheme, max scheme, row scheme

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

It is a paging scheme for splicing sql statements through a program.

The sql statement mentioned by the user does not need to write complex sql logic.

Sql provided by Nuo users is as follows

Sql code

Select * from table1

Starting from Article 5, query Article 5, and after processing, the sql becomes

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 it down.

First, change the sql statement entered by the user to modify it slightly.

After select, add top starting position + the number of messages becomes

Add a column of tempcolum, and it looks like this.

Sql code

Select top 20 tempcolumn=0,* from clazz

Nesting a layer so that you can query the travel number

That column was used here for order by.

(I don't know why the row_number function of sqlserver must be order by.)

Sql code

Select row_number () over (order by tempcolumn) temprownumber,*from (modified query) t

Cover another layer to filter out lines whose line number is less than the start position

Sql code

Select * from (layer 2) ttwhere temprownumber > 10 on how to deal with paging queries in sqlserver is shared here. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.