How SQL Server implements paging

2000:

First, get the stored procedures for all the collection of records:

Create PROCEDURE [dbo]. [P_getordernumber]
As
Select COUNT (OrderID) from orders;----Orders as Tables
Return

Paging Stored Procedures

CREATE PROCEDURE [dbo]. [p_getpagedorders2000]
(@startIndex int,---start page
@pageSize int----The number of each page displayed
)
As
SET NOCOUNT ON
Declare @indextable table (ID int identity (1,1), nid int)----Define a table variable
DECLARE @PageUpperBound int
Set @PageUpperBound = @startIndex + @pagesize-1
SET ROWCOUNT @PageUpperBound
Insert into @indextable (NID) Select OrderID from orders by OrderID DESC
Select O.orderid,o.orderdate,o.customerid,c.companyname,e.firstname+ ' +e.lastname as EmployeeName
From Orders O
Left OUTER join Customers C
On O.customerid=c.customerid
Left OUTER join Employees E
On O.employeeid=e.employeeid
INNER JOIN @indextable T on
O.orderid=t.nid
where t.id between @startIndex and @PageUpperBound order by t.id----the key to paging
SET NOCOUNT OFF

2005:

Create [dbo]. [p_getpagedorders2005]
(@startIndex INT,
@pageSize INT
)
As
Begin
With OrderList as (
SELECT row_number () over (order by O.orderid DESC) as ROW, O.orderid,o.orderdate,o.customerid,c.companyname,e.firstname + ' +e.lastname as EmployeeName
From Orders O
Left OUTER join Customers C
On O.customerid=c.customerid
Left OUTER join Employees E
On O.employeeid=e.employeeid)

SELECT Orderid,orderdate,customerid,companyname,employeename
From OrderList
WHERE Row between @startIndex and @startIndex + @pageSize-1
End