Search code examples
sqlsql-serverpaginationlimitoffset

Equivalent of LIMIT and OFFSET for SQL Server?

In PostgreSQL there is the Limit and Offset keywords which will allow very easy pagination of result sets.

What is the equivalent syntax for SQL Server?

Solution

  • The equivalent of LIMIT is SET ROWCOUNT, but if you want generic pagination it's better to write a query like this:

    ;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

    The advantage here is the parameterization of the offset and limit in case you decide to change your paging options (or allow the user to do so).

    Note: the @Offset parameter should use one-based indexing for this rather than the normal zero-based indexing.