There's a good article here by Frank Kerrigan on a way to implement paging in SQL Server 2005 by using Common Table Expressions (CTE's).
What are CTE's? This is a new feature of SQL Server 2005 that helps you eliminate temp tables and cursors in your queries. You can generate some pretty amazing queries using recursive CTE's, but that's not what Frank is trying to demonstrate. Anyway, they can be used similarly to a derived table.
Here's the syntax:
[ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
Here's an example of a CTE that uses the AdventureWorks database and returns the total number of sales order and most recent sales order date fo rall salespersons and their managers. It demonstrates how you can use a single CTE multiple times in a query.
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
So, Frank uses this kind of syntax and the ROW_NUMBER() function to cleverly implement paging:
With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30
As they say, read the whole thing.