There are already many paging query methods, and I am also a member here.
SQL Server has a Set Rowcount setting, which means that the command processing stops processing the command after responding to the specified number of rows, we can use it to implement high-performance paging query in a 10 million row-level data table. Let's talk about the implementation method:
1. Assume that there is an indexed primary key field ID (integer type) in the Table. We will retrieve data by page based on this field.
2. Place the page size in @ PageSize.
3. The current page number is stored in @ CurrentPage.
4. How can we make the record pointer quickly scroll to the row starting with the data we want to fetch? This is the key! With Set RowCount, we can easily implement it.
5. If we successfully scroll the record pointer to the row starting with the data we want to fetch, and then record the value of the record ID field in that row, then, using Top and conditions, we can easily get the data of a specified page. Of course, with Set RowCount, do we still use Top?
Let's see how Set Rowcount helps us:
Declare @ ID int
Declare @ MoveRecords int
-- @ CurrentPage and @ PageSize are input parameters
Set @ MoveRecords = @ CurrentPage * @ PageSize 1
-- The following two rows are used to quickly scroll to the row of the data we want to fetch and record the ID.
Set Rowcount @ MoveRecords
Select @ ID = ID from Table1 Order by ID
Set Rowcount @ PageSize
-- Most hate to reduce the trouble *, but it is used for convenience.
Select * From Table1 Where ID >=@ ID Order By ID
Set Rowcount 0
You can try it out. In a table with 100th million records, you can flip pages to 100 pages (entries per page) to see how fast it is!
Source: Nan crazy BLOG
Http://name-lh.cnblogs.com/archive/2006/03/08/346059.html
