Paging SQL Server Data with OFFSET FETCH

Paging before SQL Server 2012

Before OFFSET FETCH, retrieving later pages commonly involved ROW_NUMBER() in a subquery. TOP alone only selects from the beginning of a result.

-- Pendekatan lama dengan ROW_NUMBER
WITH Bernomor AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY DibuatPada DESC) AS Baris
    FROM Artikel
)
SELECT * FROM Bernomor
WHERE Baris BETWEEN 21 AND 40;

A shorter OFFSET FETCH query

SELECT *
FROM Artikel
ORDER BY DibuatPada DESC
OFFSET 20 ROWS
FETCH NEXT 20 ROWS ONLY;

OFFSET specifies how many rows to skip, and FETCH NEXT specifies how many to return afterward. ORDER BY is required. For stable paging when sort values tie, include a unique tie-breaker in the ordering.

Calculate the offset from a page number

DECLARE @Halaman INT = 3;
DECLARE @UkuranHalaman INT = 20;

SELECT *
FROM Artikel
ORDER BY DibuatPada DESC
OFFSET (@Halaman - 1) * @UkuranHalaman ROWS
FETCH NEXT @UkuranHalaman ROWS ONLY;

Performance on distant pages

A large offset still requires the database to skip earlier rows, so deep pages in large datasets may be slow. For sequential navigation, keyset pagination based on the last observed sort values is often more efficient.

Exercise

Rewrite the example using keyset pagination with WHERE DibuatPada < @DibuatPadaTerakhir, accounting for tied timestamps if needed. Compare its execution plan with OFFSET FETCH.