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.

More Related Contents:

  1. SQL Server 2008 paging methods?
  2. How do I obtain a Query Execution Plan in SQL Server?
  3. Count(*) vs Count(1) – SQL Server
  4. When should I use CROSS APPLY over INNER JOIN?
  5. Equivalent of LIMIT and OFFSET for SQL Server?
  6. LIMIT 10..20 in SQL Server
  7. INNER JOIN vs LEFT JOIN performance in SQL Server
  8. SQL JOIN vs IN performance?
  9. Is a view faster than a simple query?
  10. Multiple INSERT statements vs. single INSERT with multiple VALUES
  11. efficient way to implement paging
  12. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  13. Implement paging (skip / take) functionality with this query
  14. Does Foreign Key improve query performance?
  15. Counting DISTINCT over multiple columns
  16. Fastest way to determine if record exists
  17. SQL count(*) performance
  18. Why is a UDF so much slower than a subquery?
  19. Favourite performance tuning tricks [closed]
  20. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  21. Can an INNER JOIN offer better performance than EXISTS
  22. How to check which locks are held on a table
  23. SQL Server SELECT LAST N Rows
  24. When using GETDATE() in many places, is it better to use a variable?
  25. T-SQL Skip Take Stored Procedure
  26. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
  27. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  28. Add a row number to result set of a SQL query
  29. How to get the next number in a sequence
  30. How to Select Every Row Where Column Value is NOT Distinct

Leave a Comment