There are many methods of paging query, and I will join them here.
There is a Set Rowcount setting in SQL Server, which means that the command processing stops processing the command after responding to the specified number of rows. Using this feature, we can use it to achieve high performance in a tens of millions of row-level data table. Performance paginated queries. Let’s first talk about the implementation method:
1. Let’s assume that there is a primary key field ID (integer type) in the Table that has been indexed. We will fetch data for paging according to this field.
2. We put the page size in @PageSize
3. We put the current page number in @CurrentPage
4. How to quickly scroll the record pointer to the row 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 record the value of the ID field of the record in that row, then, using Top and conditions, we can easily get the specified page of data. 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 use it temporarily.
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