Using ROW_NUMBER()

From SQL Server 2005 Books Online:

The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.

USE AdventureWorks;
GO

WITH OrderedOrders AS
(
SELECT SalesOrderID
, OrderDate
, ROW_NUMBER() OVER (ORDER BY OrderDate) AS ‘RowNumber’
FROM Sales.SalesOrderHeader
)

SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;