Pagination with ROW_NUMBER() Window Function

intermediate

E-commerce site needs efficient pagination for product listings. Traditional OFFSET/FETCH is slow for large offsets. Solution: Use ROW_NUMBER() with filtering.

-- Traditional pagination (slow for large offsets)

SELECT product_id, name, price

FROM products

ORDER BY product_id

OFFSET 10000 ROWS

FETCH NEXT 20 ROWS ONLY;

-- Slow for large offsets (must skip 10000 rows)

-- Better: ROW_NUMBER() with filtering

WITH numbered_products AS (

SELECT

product_id,

name,

price,

ROW_NUMBER() OVER (ORDER BY product_id) AS row_num

FROM products

)

SELECT product_id, name, price

FROM numbered_products

WHERE row_num BETWEEN 10001 AND 10020;

-- Best: Keyset pagination (cursor-based)

SELECT TOP 20 product_id, name, price

FROM products

WHERE product_id > @last_product_id

ORDER BY product_id;

-- Fast for any page, no offset needed

ROW_NUMBER() enables efficient pagination. Better than OFFSET for large offsets. Keyset pagination (cursor-based) is fastest. Real-world: Stack Overflow uses ROW_NUMBER() for question pagination.

ROW_NUMBER() provides efficient pagination. Keyset pagination (cursor-based) is fastest for large datasets. Use for product listings, search results, infinite scroll.

SQL Server

ROW_NUMBER(): Better than OFFSET for large offsets. Keyset pagination: Fastest (no offset). Use for large datasets. Real-world: Stack Overflow uses keyset pagination.

graph TB

subgraph "OFFSET Pagination"

O1["OFFSET 10000 ROWS<br/>Must skip 10000 rows<br/>Slow for large offsets"]

end

subgraph "ROW_NUMBER Pagination"

R1["ROW_NUMBER() OVER (ORDER BY id)<br/>WHERE row_num BETWEEN 10001 AND 10020<br/>Faster"]

end

subgraph "Keyset Pagination"