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

This article mainly explains "what are the paging methods for SQL server". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what paging methods SQL server has.

This article is about the paging method of SQL server, using the SQL server 2012 version. Below, pageIndex is used to indicate the number of pages, and pageSize represents the records contained in a page. And the following involves specific examples, set the query page 2, each page contains 10 records.

First of all, let's talk about the difference between SQL server paging and MySQL paging. Mysql pagination is directly done with limit (pageIndex-1), pageSize can be completed, but SQL server does not have the limit keyword, only the top keyword similar to limit. So it's troublesome to paginate.

There are only four kinds of    SQL server paging I know: triple loop; using max (primary key); using row_number keyword, offset/fetch next keyword (summed up by collecting other people's methods on the Internet, should be the only four methods of thinking, other methods are based on this transformation).

Partial records of the student table to be queried

Method 1: triple cycle

   first take the first 20 pages, then reverse the order, and take the first 10 records after the reverse order, so that you can get the data needed for paging, but the order is reversed, and then you can reverse the order, or you can no longer sort it and give it directly to the front end for sorting.

   also has a method that also belongs to this type, here do not put the code out, just talk about the train of thought, that is, first query out the first 10 records, and then use not in to exclude these 10, and then query.

Code implementation-set execution time to start, used to check the performance of the set statistics time on;-- paging query (general) select * from (select top pageSize * from (select top (pageIndex*pageSize) * from student order by sNo asc)-- in which this layer must be sorted in ascending order. If omitted, the query results are incorrect. As temp_sum_student order by sNo desc) temp_orderorder by sNo asc-- pagination query page 2, there are 10 records select * from (select top 10 * from (select top 20 * from student order by sNo asc) on each page-this layer must be sorted in ascending order. If omitted, the query result is incorrect. As temp_sum_student order by sNo desc) the result and time of the temp_orderorder by sNo asc; query

Method 2: use max (primary key)

   first top the first 11 rows of records, then use max (id) to get the largest id, and then re-query the first 10 entries in this table, but with conditions, where id > max (id).

The code implements set statistics time on;-- paging query (general type) select top pageSize * from student where sNo > = (select max (sNo) from (select top ((pageIndex-1) * pageSize+1) sNofrom student order by sNo asc) temp_max_ids) order by sNo;-- paging query page 2, with 10 records per page select top 10 * from student where sNo > = (select max (sNo) from (select top 11 sNofrom student order by sNo asc) temp_max_ids) order by sNo; query results and time

Method 3: use the row_number keyword

   directly uses the row_number () over (order by id) function to calculate the number of trips, and select the corresponding number of rows to return, but this keyword is only available in SQL server version 2005 or above.

SQL implements set statistics time on;-- paging query (general type) select top pageSize * from (select row_number () over (order by sno asc) as rownumber,* from student) temp_rowwhere rownumber > ((pageIndex-1) * pageSize); set statistics time on;-- pagination query page 2, each page has 10 records select top 10 * from (select row_number () over (order by sno asc) as rownumber,* from student) temp_rowwhere rownumber > 10; the result and time of the query

The fourth method: offset / fetch next (version 2012 or above) code to implement set statistics time on;-- paging query (general) select * from studentorder by sno offset ((@ pageIndex-1) * @ pageSize) rowsfetch next @ pageSize rows only;-- paging query page 2, each page has 10 records select * from studentorder by sno offset 10 rowsfetch next 10 rows only

Offset A rows, strip out the first A record, fetch next B rows only, read B data backward.

Result and run time

Encapsulated stored procedure

Finally, I encapsulated a paging stored procedure that is convenient for everyone to call, so that when paging is written, the stored procedure can be called directly.

Paged stored procedure

Create procedure paging_procedure (@ pageIndex int,-- Page @ pageSize int-- number of records per page) asbegin select top (select @ pageSize) *-- Note here that variables cannot be placed directly here. To use select from (select row_number () over (order by sno) as rownumber,* from student) temp_row where rownumber > (@ pageIndex-1) * @ pageSize Just call end-- directly at that time. Execute the following statement to summarize the paged stored procedure exec paging_procedure @ pageIndex=2,@pageSize=10;

According to the execution time of the above four paging methods,    can know that among the above four paging methods, the second, third and third four methods have the same performance, but the first one is very poor and is not recommended. In addition, this blog has tested a small amount of data and has not paged a large amount of data yet, so it is not clear which method performs better when a large amount of data is to be paged. I recommend the fourth, after all, the fourth is the new method launched by SQL server after the upgrade, so it should have better performance and readability in theory.

Thank you for your reading, the above is the content of "what are the paging methods of SQL server". After the study of this article, I believe you have a deeper understanding of what paging methods SQL server has, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.