Efficiently Paginating Results in SQL Server

Paginating Results in SQL Server

When working with large datasets in SQL Server, it’s often necessary to display data in smaller, manageable chunks – a process known as pagination. This tutorial will explore different approaches to pagination in SQL Server, from traditional methods to more modern techniques, with a focus on performance and efficiency. We’ll also cover how to retrieve the total number of records for complete pagination control.

The Core Concepts

Pagination involves dividing a large result set into discrete "pages," each containing a limited number of records. Key components include:

• Page Number: The current page the user is viewing.
• Page Size: The number of records to display per page.
• Total Records: The total number of records in the entire result set.
• Offset: The starting point (row number) for retrieving records for a specific page. Calculated as (Page Number - 1) * Page Size.

Traditional Pagination with ROW_NUMBER()

A common approach to pagination is to use the ROW_NUMBER() window function. This function assigns a unique sequential integer to each row within a result set based on a specified order.

Here’s how it works:

WITH OrderedResults AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RowNum,
        *
    FROM
        YourTable
    WHERE
        -- Your filtering criteria
)
SELECT
    *
FROM
    OrderedResults
WHERE
    RowNum BETWEEN (PageNumber - 1) * PageSize + 1 AND PageNumber * PageSize;

In this example:

1. The WITH clause defines a Common Table Expression (CTE) called OrderedResults.
2. ROW_NUMBER() OVER (ORDER BY SomeColumn) assigns a unique row number to each record, ordered by the SomeColumn.
3. The final SELECT statement filters the results based on the desired page number and page size.

Retrieving the Total Count: To determine the total number of records before pagination, you can execute a separate COUNT(*) query:

SELECT COUNT(*) FROM YourTable WHERE -- Your filtering criteria;

Modern Pagination with OFFSET and FETCH (SQL Server 2012 and later)

SQL Server 2012 introduced a more concise and often more efficient syntax for pagination using the OFFSET and FETCH clauses.

SELECT *
FROM YourTable
ORDER BY SomeColumn
OFFSET (PageNumber - 1) * PageSize ROWS
FETCH NEXT PageSize ROWS ONLY;

This query directly specifies the number of rows to skip (OFFSET) and the number of rows to retrieve (FETCH). It’s cleaner and can be optimized by the SQL Server query optimizer in many cases.

Important Considerations:

• ORDER BY is mandatory when using OFFSET and FETCH.
• You cannot combine TOP with OFFSET and FETCH in the same query.

Retrieving the Total Count: The same COUNT(*) query used previously can be used to retrieve the total number of records before pagination.

The "Seek Method" (Keyset Pagination) for High Performance

For very large datasets, the OFFSET and FETCH and ROW_NUMBER() methods can become slow, particularly when requesting higher page numbers. This is because SQL Server may need to scan a large number of rows to reach the correct offset. The "seek method" (also known as keyset pagination) offers a significant performance improvement.

Instead of using an offset, the seek method uses the values from the last record of the previous page to filter the results for the next page. This allows SQL Server to use indexes to quickly locate the starting point for the next page.

SELECT TOP PageSize *
FROM YourTable
WHERE (SomeColumn < @PreviousSomeColumn) OR (SomeColumn = @PreviousSomeColumn AND PrimaryKeyColumn < @PreviousPrimaryKeyColumn)
ORDER BY SomeColumn, PrimaryKeyColumn;

In this example:

• @PreviousSomeColumn is the value of the SomeColumn from the last record of the previous page.
• @PreviousPrimaryKeyColumn is the value of the primary key column from the last record of the previous page.
• This query selects the top PageSize records that meet the specified criteria.

Advantages of the Seek Method:

• Performance: Significantly faster for high page numbers.
• Stability: Pages remain consistent even if underlying data changes (assuming the ordering column doesn’t change).

Disadvantages of the Seek Method:

• Requires storing the last record’s values from the previous page.
• Can’t directly jump to a specific page number without fetching the previous pages.

Retrieving the Total Count: You can still use COUNT(*) to get the total record count, or utilize window functions like COUNT(*) OVER() to include the total count in the results.

Choosing the Right Method

The best pagination method depends on your specific requirements and the size of your dataset.

• Small to Medium Datasets: OFFSET and FETCH or ROW_NUMBER() are generally sufficient.
• Large Datasets and High Performance Requirements: The seek method is the preferred choice.
• Need to Jump to Specific Pages: OFFSET and FETCH or ROW_NUMBER() are easier to implement.