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
