Recently, INDN MAGAZINE 10 Tips for Writing High-Performance Web Applications referred to the importance of effective data paging technology to improve the performance of the ASP .NET program; and give an example of Stored Procedure, which implement data paging, as follows:
Create Procedure Northwind_Orderspaged
(
@PageIndex Int,
@PageSize Int
)
AS
Begin
Declare @PagelowerBound Int
Declare @PAGEUPPERBOUND INT
Declare @rowstoreTurn Int
- First set 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 INTITENTITY (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, since there is no way to perform RANKING operations, this example first creates a temporary table with the Identity field, using the self-growth characteristics of the Identity field, indirectly pressing each line of the OrderS table. Give a line number, then implement paging based on this line number.
In SQL Server 2005, since the system provides a built-in Ranking function, in order to generate a line number to the OrderS table, we no longer need to take advantage of the Identity field.
For example, using SQL Server 2005's ROW_NUMBER () function, press the ORDERID field in reverse sequence, to generate a line number to the OrderS table, 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 make more convenient implementation of the paging operations.
About the T-SQL new features of SQL Server 2005, see documentation: http://msdn.microsoft.com/sql/archive/default.aspx? Pull = / library / en-US / DNSQL90 / HTML / SQL_05TSQLENHANCE.ASP
