Efficient Pagination in SQL Server: Modern Best Practices

When building applications that display large datasets—like e-commerce product grids or search results—loading millions of rows at once is a performance nightmare. Pagination solves this by breaking the dataset into smaller, manageable chunks (or pages).

In SQL Server, the most efficient and modern way to handle this is using the OFFSET ... FETCH clause.

The Modern Standard: `OFFSET FETCH`

Introduced in SQL Server 2012, OFFSET FETCH is an extension of the ORDER BY clause. It allows you to skip a specific number of rows (OFFSET) and then return a specific number of rows (FETCH).

The Core Syntax

SQL

SELECT Column1, Column2
FROM YourTable
ORDER BY SortColumn -- ORDER BY is MANDATORY
OFFSET RowsToSkip ROWS
FETCH NEXT PageSize ROWS ONLY;

Crucial Rule: You must use an ORDER BY clause with OFFSET FETCH. Without a deterministic sort order, SQL Server cannot reliably know which rows to skip or fetch.
Trending
Advanced TRY CATCH in SQL Server for Error Control

Step-by-Step Example

Let’s say you have an Employees table, and your application wants to display 10 records per page.

To make this dynamic in a stored procedure or application backend, you can calculate the OFFSET using two variables: @PageNumber and @PageSize.

SQL

DECLARE @PageNumber INT = 3; -- The page the user wants to see
DECLARE @PageSize INT = 10;  -- Rows per page

SELECT EmployeeID, FirstName, LastName, HireDate
FROM Employees
ORDER BY HireDate DESC, EmployeeID -- Tied orders are resolved by EmployeeID
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

How the Math Works for Page 3:

@PageNumber - 1 becomes 2.
2 * 10 becomes 20.
SQL Server will skip the first 20 rows and fetch the next 10 rows (Rows 21–30).

Performance Tips for Production

While OFFSET FETCH is incredibly clean, keep these two performance guidelines in mind for large-scale databases:

Index Your Sort Columns: If you are ordering by HireDate DESC, ensure you have an index on HireDate. If SQL Server has to sort millions of rows in memory before it can apply the OFFSET, your query will slow down significantly.
Beware of Deep Paging: As the page number gets higher (e.g., page 10,000), SQL Server still has to read through all the skipped rows to find where the offset ends. For massive datasets where users skip deep into the data, consider using Keyset Pagination (the “seek method”), which remembers the last ID of the previous page and uses a WHERE ID > @LastID clause instead of skipping rows.