How to Paginate Results in SQL Server with OFFSET FETCH

When a query in SQL Server returns thousands of rows, showing them all at once is impractical: it is better to split the data into pages. The simplest and most readable way to paginate results in SQL Server is the OFFSET FETCH clause, available since SQL Server 2012 and also in Azure SQL Database. Below is a practical, step-by-step example, plus the most common error to avoid. OFFSET says how many rows to skip, and FETCH how many to return next.

Prerequisites

• A SQL Server 2012 or later instance (it also works on Azure SQL Database).
• SQL Server Management Studio (SSMS) or Azure Data Studio to run the queries.
• A table with several rows to test — the example uses dbo.Encomendas.
• Basic knowledge of SELECT and ORDER BY.

Step 1: Understand why ORDER BY is mandatory

The OFFSET FETCH clause works in two stages: first it "skips" a number of rows and then it "fetches" the following rows. For skipping and fetching to mean anything, SQL Server needs a well-defined order — that is why OFFSET can only appear after an ORDER BY. Without ordering, the engine does not guarantee which row comes first, and pagination would be unpredictable.

Common error: writing OFFSET without ORDER BY. In that case SQL Server returns a syntax error, because OFFSET is part of the ORDER BY clause itself. Always sort before skipping rows.

Step 2: Learn the OFFSET FETCH syntax

The structure is always the same: you sort, skip with OFFSET, and fetch a block with FETCH NEXT. This example returns the 10 most recent orders:

SELECT EncomendaID, Cliente, DataEncomenda, Total
FROM dbo.Encomendas
ORDER BY DataEncomenda DESC, EncomendaID DESC
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

OFFSET 0 ROWS means "do not skip any row" and FETCH NEXT 10 ROWS ONLY means "return only the next 10". The keywords ROWS/ROW and NEXT/FIRST are synonyms — pick whichever reads better.

Step 3: Compute the page with a simple formula

To jump to any page you only need two values: the page number and the page size. The number of rows to skip is (page - 1) * size. This example shows page 3, with 10 records per page, that is, it skips the first 20 rows:

DECLARE @Pagina  INT = 3;
DECLARE @Tamanho INT = 10;

SELECT EncomendaID, Cliente, DataEncomenda, Total
FROM dbo.Encomendas
ORDER BY DataEncomenda DESC, EncomendaID DESC
OFFSET (@Pagina - 1) * @Tamanho ROWS
FETCH NEXT @Tamanho ROWS ONLY;

By changing only the value of @Pagina to 1, 2, 3… you walk through all the results, block by block. This is exactly the pattern running behind a table with "Previous" and "Next" buttons or an API that returns paged data. Keep @Tamanho the same across pages so navigation stays consistent.

Step 4: Sort by a unique column for stable pagination

If you sort only by a column with repeated values (for example, only by DataEncomenda), two orders with the same date can swap positions between runs and end up on the wrong page — or appear twice. The fix is to always add a unique column as a tie-breaker, usually the primary key:

ORDER BY DataEncomenda DESC, EncomendaID DESC

With a unique tie-breaker the order becomes deterministic and each row appears on exactly one page.

Check the result

Run the query with @Pagina = 1 and then with @Pagina = 2: each one should return 10 rows and there must be no repeated record between the two pages. To find out how many pages exist in total, divide the number of rows by the page size and round up:

DECLARE @Tamanho INT = 10;

SELECT CEILING(COUNT(*) * 1.0 / @Tamanho) AS TotalPaginas
FROM dbo.Encomendas;

If the last page returns fewer than 10 rows, everything is correct: it is simply the remainder of the records.

Conclusion

With ORDER BY plus OFFSET FETCH you get readable, standardized, easy-to-maintain pagination, ideal for feeding UI tables and APIs. For very deep pages — when you skip tens of thousands of rows — OFFSET can become slow; in that scenario it is worth studying keyset pagination, which pages from the last key read. This approach is part of the ANSI SQL standard, so the same reasoning applies to other engines such as PostgreSQL. A good next step is to create an index on your ORDER BY columns and compare the response time. Which of your heaviest queries would you paginate first?