Row Offset in SQL Server

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!

Solution 1:

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

Solution 2:

If you will be processing all pages in order then simply remembering the last key value seen on the previous page and using TOP (25) ... WHERE Key > @last_key ORDER BY Key can be the best performing method if suitable indexes exist to allow this to be seeked efficiently - or an API cursor if they don't.

For selecting an arbitary page the best solution for SQL Server 2005 - 2008 R2 is probably ROW_NUMBER and BETWEEN

For SQL Server 2012+ you can use the enhanced ORDER BY clause for this need.

SELECT  *
FROM     MyTable 
ORDER BY OrderingColumn ASC 
OFFSET  50 ROWS 
FETCH NEXT 25 ROWS ONLY 

Though it remains to be seen how well performing this option will be.

Solution 3:

This is one way (SQL2000)

SELECT * FROM
(
    SELECT TOP (@pageSize) * FROM
    (
        SELECT TOP (@pageNumber * @pageSize) *
        FROM tableName 
        ORDER BY columnName ASC
    ) AS t1 
    ORDER BY columnName DESC
) AS t2 
ORDER BY columnName ASC

and this is another way (SQL 2005)

;WITH results AS (
    SELECT 
        rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
        , *
    FROM tableName 
) 
SELECT * 
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

Solution 4:

You can use ROW_NUMBER() function to get what you want:

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM tbl) t
WHERE RowNr BETWEEN 10 AND 20