After years of torturing their SQL Server users, Microsoft provided a “better” way of paging records in version 2005, and it is a weird one! The following example is from their website, an article about ROW_NUMBER (Transact-SQL).
WITH OrderedOrders AS
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
WHERE RowNumber BETWEEN 50 AND 60;
It is simply unacceptable, non-standard, impractical! By the way, you can not use column numbers (e.g. ORDER …