The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

OFFSET and FETCH: Split a sorted result into pages

A search screen rarely displays thousands of rows at once. We need to request a portion of the result: the first page, the second page, or any subsequent block.

OFFSET indicates how many rows should be skipped and FETCH how many to return. Both depend on a stable ORDER BY.

Syntax

SELECT Columna1, Columna2
FROM dbo.Tabla
ORDER BY ColumnaDeOrden
OFFSET FilasAOmitir ROWS
FETCH NEXT TamanoPagina ROWS ONLY;

Example: paginate products

DECLARE @NumeroPagina INT = 2;
DECLARE @TamanoPagina INT = 2;

SELECT
    p.IdProducto,
    p.Nombre,
    p.Precio
FROM dbo.Producto AS p
WHERE p.Activo = 1
ORDER BY
    p.Precio,
    p.IdProducto
OFFSET (@NumeroPagina - 1) * @TamanoPagina ROWS
FETCH NEXT @TamanoPagina ROWS ONLY;

DECLARE creates the two reusable values; its syntax is explained in DECLARE, SET, and SELECT.

Page 2 skips the first two rows and returns the next two. IdProducto completes the order when multiple products have the same price.

Get total for interface

A window function can include the total count before pagination. These functions calculate over related rows without reducing them to one:

COUNT(*) OVER () counts all rows that match the filter without grouping them. The empty parentheses in OVER indicate that the result is not divided into groups or a different order is applied for that calculation.

SELECT
    p.IdProducto,
    p.Nombre,
    p.Precio,
    COUNT(*) OVER () AS TotalResultados
FROM dbo.Producto AS p
WHERE p.Activo = 1
ORDER BY
    p.Precio,
    p.IdProducto
OFFSET (@NumeroPagina - 1) * @TamanoPagina ROWS
FETCH NEXT @TamanoPagina ROWS ONLY;

Each row on the page will repeat TotalResultados. If the requested page is out of range and does not return rows, we will not have that total either; The application must consider that case.

Validate parameters

A page smaller than 1 or a negative size does not represent a valid request. We can normalize or reject those values ​​before executing the query:

IF @NumeroPagina < 1
    SET @NumeroPagina = 1;

IF @TamanoPagina < 1 OR @TamanoPagina > 100
    SET @TamanoPagina = 25;

The maximum limit prevents pagination from being accidentally used as an uncontrolled download.

What should be checked

Offset pagination can become expensive on distant pages because SQL Server must still locate and skip preceding rows. For deep traversal, it may be better to paginate from the last key seen. This keyset pagination uses the last value received as the starting point for the next query.

We must also consider concurrent changes: changes made by other sessions between one request and the next. If rows are inserted or deleted, the same row can move between pages. A deterministic order defines a stable sequence for the same set, but does not turn several queries into an immutable snapshot.