Using stored procedures to perform paging with high performance in SQL Server_PHP Tutorial

There are many methods of paging query, and I will join them here.
There is a Set Rowcount setting in SQL Server. It means that the command processing stops processing the command after responding to the specified number of rows. Taking advantage of this feature, we can borrow it to create a data table with tens of millions of rows. Implement high-performance paging queries. Let’s talk about the implementation method first:
1. Let’s assume that there is a primary key field ID (integer type) that has been indexed in the Table. We will fetch data according to this field for paging.
2. We put the page size in @PageSize
3. We put the current page number in @CurrentPage
4. How to make the record pointer quickly scroll to the line at the beginning of the data we want to retrieve? , this is the key! With Set RowCount, we can easily implement it.
5. If we successfully scroll the record pointer to the row at the beginning of the data we want to fetch, and then we record the value of the ID field of the record in that row, then, using Top and conditions, we can easily The data of the specified page is obtained. Of course, with Set RowCount, do we still use Top?
Let’s see how Set Rowcount can help us:
Declare @ID int
Declare @MoveRecords int
--@CurrentPage and @PageSize are the incoming parameters
Set @MoveRecords= @CurrentPage * @PageSize 1
--The following two lines enable quick scrolling to the row of data we want to retrieve, and record the ID
Set Rowcount @MoveRecords
Select @ID=ID from Table1 Order by ID
Set Rowcount @PageSize
--I hate using * to reduce trouble, but for the convenience of explanation, I will temporarily use
Select * From Table1 Where ID>=@ID Order By ID
Set Rowcount 0
You can try it. In a table with 1,000W records, you can quickly turn the page to page 100 (100 entries per page) and see how fast it is!
Source: Nanfeng BLOG
http://name-lh.cnblogs.com/archive/2006/03/08/346059.html

http://www.bkjia.com/PHPjc/631086.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631086.htmlTechArticleThere are many ways to query by page, and here I am also one of them. There is a Set Rowcount setting in SQL Server, which means that the command processing is specified in the response...