What is the best way to paginate results in SQL Server

by

Finally, Microsoft SQL Server 2012 was released, I really like its simplicity for a pagination, you don’t have to use complex queries like answered here.

For getting the next 10 rows just run this query:

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned

Key points to consider when using it:

  • ORDER BY is mandatory to use OFFSET ... FETCH clause.
  • OFFSET clause is mandatory with FETCH. You cannot use ORDER BY ...
    FETCH    .
  • TOP cannot be combined with OFFSET and FETCH in the same query
    expression.

Leave a Comment