Tune Pagination for Millions of Rows

Scenario

A table contains millions of records, and users navigate through the data page by page.

Traditional pagination can become slow for later pages because SQL Server must sort and skip an increasing number of rows.

1. Avoid deep OFFSET/FETCH pagination

SELECT OrderId, CustomerId, OrderDate, TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC
OFFSET 1000000 ROWS
FETCH NEXT 20 ROWS ONLY;

Even though only 20 rows are returned, SQL Server may process and skip one million rows.

OFFSET/FETCH is acceptable for small datasets or early pages, but deep pages can be expensive.

2. Use keyset pagination

Keyset pagination retrieves records after the last record from the previous page.

First page

SELECT TOP (20)
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderId DESC;

Assume the last record returned contains:

OrderDate = 2026-09-10
OrderId   = 50000

Next page

SELECT TOP (20)
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE OrderDate < @LastOrderDate
   OR (
        OrderDate = @LastOrderDate
        AND OrderId < @LastOrderId
      )
ORDER BY OrderDate DESC, OrderId DESC;

This allows SQL Server to continue from the previous position instead of skipping thousands or millions of rows.

3. Create a supporting index

The index should follow the filtering and sorting columns.

CREATE INDEX IX_Orders_OrderDate_OrderId
ON dbo.Orders(OrderDate DESC, OrderId DESC)
INCLUDE (CustomerId, TotalAmount);

This index can:

• Support the WHERE condition

• Provide the required sort order

• Avoid unnecessary sorting

• Cover the selected columns

• Reduce key lookups

Do not include every table column because it increases index size and write cost.

4. Use a unique and stable sort order

Ordering only by OrderDate may produce inconsistent pages because multiple rows can have the same date.

Use a unique secondary column:

ORDER BY OrderDate DESC, OrderId DESC;

This prevents missing or duplicate records between pages.

5. Keep filters SARGable

Avoid:

WHERE YEAR(OrderDate) = 2026;

Prefer:

WHERE OrderDate >= '20260101'
  AND OrderDate <  '20270101';

SARGable conditions allow SQL Server to use indexes efficiently.

6. Avoid calculating the total count on every request

This can be expensive:

SELECT COUNT(*)
FROM dbo.Orders;

Possible alternatives:

• Calculate it only when required

• Cache the count

• Display “Load more” instead of total pages

• Maintain a summary table where appropriate

• Return whether another page exists

To detect another page, retrieve one extra row:

SELECT TOP (21)
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE OrderDate < @LastOrderDate
   OR (
        OrderDate = @LastOrderDate
        AND OrderId < @LastOrderId
      )
ORDER BY OrderDate DESC, OrderId DESC;

Return 20 records and use the 21st record to determine whether another page exists.

When to use each method

Interview Answer

For a table containing millions of rows, I would prefer keyset pagination instead of deep OFFSET/FETCH. I would pass the last retrieved sorting values to the next request and create a covering index matching the filter and ORDER BY columns. I would also use a unique, stable sort order, keep predicates SARGable and avoid calculating COUNT(*) on every request.

Key Points

• Deep OFFSET values become progressively slower.

• Keyset pagination performs better for large datasets.

• Create an index matching the filter and sorting order.

• Always use deterministic ordering.

• Avoid unnecessary total-count queries.