SQL Server 2005: Efficient data paging operations with new ranking functions

Source: Internet
Author: User
Tags rowcount
A recent article on MSDN Magazine on the Tips for writing high-performance Web applications mentions effective data paging technology for improving ASP. NET program performance, and gives an example of stored procedure to implement data paging, which is reproduced as follows:
CREATE PROCEDURE northwind_orderspaged
(
@PageIndex int,
@PageSize int
)
As
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
--The ROWCOUNT
SET @RowsToReturn = @PageSize * (@PageIndex 1)
SET RowCount @RowsToReturn
--Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound @PageSize 1
--Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexID int IDENTITY (1, 1) not NULL,
OrderID int
)
--Insert into the temp table
INSERT into #PageIndex (OrderID)
SELECT
OrderID
From
Orders
ORDER BY
OrderID DESC
--Return Total count
SELECT COUNT (OrderID) from Orders
--Return paged results
SELECT
O.*
From
Orders O,
#PageIndex PageIndex
WHERE
O.orderid = Pageindex.orderid and
Pageindex.indexid > @PageLowerBound and
Pageindex.indexid < @PageUpperBound
ORDER BY
Pageindex.indexid
End
In SQL Server 2000, because there is no effective method for ranking operations, the example first creates a temporary table with an identity field, taking advantage of the self-growth characteristics of the identity field. Indirectly, a line number is assigned to each row of the Orders table by OrderID, and then pagination is implemented based on this line number.
In SQL Server 2000, because the system provides a built-in ranking function, we no longer need to take advantage of the identity field in order to generate line numbers for the Orders table.
For example, using the Row_number () function of SQL Server 2000, in reverse order by OrderID field, the statement that generates line numbers for the Orders table is as follows:

SELECT row_number () over (order by ordered DESC) as RowNum, ordered
From Orders
ORDER BY RowNum DESC
Based on these new ranking functions, you can easily implement the paging operation of the data.
For SQL Server 2005 's new T-SQL features, see the Documentation:
Http://msdn.microsoft.com/sql/archive/default.aspx?pull=/library/en-us/dnsql90/html/sql_05tsqlenhance.asp


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.