What is the best way to paginate results in SQL Server

Applying OFFSET FETCH for Efficiency in Pagination

OFFSET FETCH, a feature in SQL Server 2012, is a handy tool that makes SQL query pagination much easier. Here's some insight on how to use it effectively for your advantage:

Ensure ORDER BY Clause is Present

To correctly implement OFFSET and FETCH, your query should have an ORDER BY clause. This clause establishes the essential row order.

Skipping Rows With OFFSET

The OFFSET clause allows you to skip a certain number of rows, helping you land directly on your page of interest.

Using FETCH to Retrieve Page Results

The FETCH NEXT clause fetches specified rows - effectively controlling your page size. Use FETCH NEXT X ROWS ONLY to set your page size.

Avoiding The TOP Clause

TOP when combined with OFFSET and FETCH could lead to less efficient queries. For a more optimal performance, stick to OFFSET and FETCH.

Advanced Pagination Strategies

In addition to OFFSET FETCH, there are several other strategies for effective pagination in SQL Server. Let's explore some important ones:

Keyset Pagination (Seek Method)

For large datasets, you can use the Seek Method or Keyset Pagination. This method uses the primary key to navigate through pages and is very performant:

SELECT * FROM YourTable -- I'm watching you, data!
WHERE YourPrimaryKey > @LastPageLastValue -- Skips the history lesson and gets right to the recent stuff!
ORDER BY YourPrimaryKey -- OCD satisfied: everything is in order again!
FETCH NEXT 10 ROWS ONLY; -- I'll take 10 for now, I'm on a data diet!


The Seek Method allows for more efficient row traversal and avoids the performance downsides of OFFSET.

ROW_NUMBER() for Complex Sorts

If your ORDER BY clause involves multiple columns or expressions, ROW_NUMBER() functions prove helpful. By assigning sequential numbers to each row, it creates a defined order that can be paginated against:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY YourColumn) AS RowNum, *
    FROM YourTable
) AS PaginatedTable
WHERE RowNum BETWEEN 11 AND 20; -- Picks up in the story from the 11th row!


Counting Total Records

To get the total number of pages, use SELECT COUNT(*) while maintaining the same WHERE clause as your pagination query. Coupled with the right indexing, it helps avoid full table scans and subsequent performance issues.

Optimising Application Level Pagination

While mastering SQL queries is important, optimizing application level pagination also significantly contributes to a smooth user experience.

Maintain State Using Row Values

To enhance performance, use the values of the last retrieved row in the WHERE clause when fetching the next set of results. This method eliminates the need for OFFSET and offers a comfortable way of state maintenance.

Implementing Lazy Loading

In web applications, employ lazy loading for user-friendly UI/UX - the next set of results only loads as the user scrolls down. It also harmonizes with the Seek Method, allowing for incremental data retrieval.

Tailoring Indexes

Design indexes that cater to both the ORDER BY clause and conditions within the WHERE clause to boost the efficiency of counting and pagination operations.

Handling Data Changes

While paginating, you might experience data inserts, updates, or deletes. A well-planned pagination approach handles this flux effectively using methods like the Seek Method or snapshot isolation levels to maintain consistency.