Logo
Back

What is the best way to paginate results in SQL Server?

Paginating results is crucial when dealing with large datasets, as it helps you display records in manageable chunks. Below are the two primary methods commonly used in SQL Server.

1. Using OFFSET and FETCH (SQL Server 2012+)

Starting with SQL Server 2012, you can use the OFFSET and FETCH clauses with an ORDER BY for a clean, concise pagination approach:

SELECT Column1, Column2
FROM YourTable
ORDER BY SomeColumn
OFFSET (@PageSize * (@PageNumber - 1)) ROWS
FETCH NEXT @PageSize ROWS ONLY;
  • ORDER BY: Mandatory when using OFFSET/FETCH.
  • OFFSET: Skips a specified number of rows.
  • FETCH NEXT: Retrieves the next set of rows.
  • @PageSize: Number of rows per page.
  • @PageNumber: Current page number.

Advantages

  • Simple and Readable: One query handles skipping and fetching.
  • Efficient: Eliminates the need for extra subqueries or complex window functions in straightforward use cases.

2. Using ROW_NUMBER() for Flexible Pagination

If you’re on an older version of SQL Server or need more control, you can use the ROW_NUMBER() window function:

WITH OrderedResults AS (
    SELECT Column1, Column2,
           ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RowNum
    FROM YourTable
)
SELECT Column1, Column2
FROM OrderedResults
WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1
                AND (@PageNumber * @PageSize);
  • ROW_NUMBER: Assigns a unique sequential integer to each row within a partition of a result set.
  • WHERE Clause: Filters rows based on page boundaries.

Advantages

  • Backward Compatibility: Works in SQL Server versions prior to 2012.
  • Flexible: Easy to include additional window functions or complex logic in the same CTE.

Performance Considerations

  1. Indexing: Ensure the column(s) used in ORDER BY have appropriate indexes to avoid scanning the entire table.
  2. Large Offsets: Extremely large OFFSET values can degrade performance because SQL Server still has to count or skip many rows.
  3. Pagination vs. Seek Method: For extremely large datasets, consider using a keyset (seek) approach, where you remember the last key from the previous page rather than skipping rows, which can be more efficient.

Recommended Resources to Advance Your SQL Skills

  1. Grokking SQL for Tech Interviews – Master essential query patterns, performance tuning, and interview-focused problems.
  2. Grokking Database Fundamentals for Tech Interviews – Dive into indexing, normalization, and transaction handling, which also factor into pagination performance.

For additional free tutorials, visit the DesignGurus.io YouTube channel. If you’re preparing for technical interviews, consider their Mock Interviews for personalized feedback from ex-FAANG engineers.

Conclusion

The preferred way to paginate in modern SQL Server versions (2012+) is to use OFFSET and FETCH clauses for simplicity and efficiency. For older versions or scenarios requiring more complex logic, ROW_NUMBER() with a CTE remains a robust alternative. Remember to optimize indexes and consider potential performance bottlenecks when dealing with very large offsets.

CONTRIBUTOR
TechGrind