SQL Server uses stored procedures for high-performance paging

Source: Internet
Author: User
Tags rowcount
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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.