Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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?

1 Answer

0 votes
by (40.4k points)

SET ROWCOUNT is equivalent of LIMIT, but if you want the generic pagination then it's better to write the query this way:

;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 of using the above code 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 can use one-based indexing for this rather than the normal zero-based indexing.

Related questions

0 votes
1 answer
asked Jul 16, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 19, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 11, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer

Browse Categories

...