Question
Is there any way in SQL Server to get the results starting at a given offset? For example, in another type of SQL database, it's possible to do:
SELECT * FROM MyTable OFFSET 50 LIMIT 25
to get results 51-75. This construct does not appear to exist in SQL Server.
How can I accomplish this without loading all the rows I don't care about? Thanks!
Answer
I would avoid using SELECT *. Specify columns you actually want even though
it may be all of them.
SQL Server 2005+
SELECT col1, col2
FROM (
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow
SQL Server 2000
Efficiently Paging Through Large Result Sets in SQL Server 2000
A More Efficient Method for Paging Through Large Result Sets
