SwePeso (12/6/2010)
It seems the dual TOP/ORDER BY was the most efficient approach by the time.I tested the new Denali OFFSET/FETCH and I got the exact same number of reads for @StartRow = 5000, but 2 ms instead of 4 ms.
SELECTNumber
FROMdbo.TallyNumbers
ORDER BYNumber
OFFSET@StartRow - 1 ROWS
FETCH NEXT50 ROWS ONLY
This is unreleased SQL Server 2011 functionality only, right?