How to Implement Paging in SQL Server with OFFSET-FETCH

SQL Server 2012 introduced the OFFSET-FETCH clause, providing a standard and efficient way to implement paging in your queries.

This approach simplifies retrieving data in chunks for web applications, reports, and APIs. Let's take a look at how it works.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
OFFSET N ROWS
FETCH NEXT M ROWS ONLY;

Where:

• N is the number of rows to skip
• M is the number of rows to return

Simple Paging Example

-- Get page 3 of products (10 items per page)
SELECT 
    ProductID,
    ProductName,
    UnitPrice
FROM 
    Products
ORDER BY 
    ProductName
OFFSET 20 ROWS        -- Skip first 20 rows (pages 1-2)
FETCH NEXT 10 ROWS ONLY; -- Get 10 rows for page 3

Calculating OFFSET Value

For page-based navigation:

DECLARE 
    @PageNumber INT = 3,
    @RowsPerPage INT = 10;

SELECT 
    ProductID, 
    ProductName,
    UnitPrice
FROM 
    Products
ORDER BY 
    ProductName
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;

Important Requirements

1. OFFSET-FETCH requires an ORDER BY clause
2. ORDER BY must specify a unique sort order for reliable paging

-- Poor practice (not guaranteed unique order)
ORDER BY Category

-- Better practice (guarantees unique sort order)
ORDER BY Category, ProductID

Paging with Total Count

A common requirement is to return both the page of data and the total count:

DECLARE 
    @PageNumber INT = 3,
    @RowsPerPage INT = 10;

-- Get total count for pagination UI
SELECT COUNT(*) AS TotalCount FROM Products;

-- Get page data
SELECT 
    ProductID, 
    ProductName,
    UnitPrice
FROM 
    Products
ORDER BY 
    ProductName
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;

Implementing in a Stored Procedure

CREATE PROCEDURE GetProductsPage
    @PageNumber INT = 1,
    @RowsPerPage INT = 10,
    @SortColumn NVARCHAR(50) = 'ProductName',
    @SortDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
    -- Validate input
    IF @PageNumber < 1 SET @PageNumber = 1;
    IF @RowsPerPage < 1 SET @RowsPerPage = 10;
    
    -- Get total count
    SELECT COUNT(*) AS TotalCount FROM Products;
    
    -- Build dynamic SQL for sorting
    DECLARE @SQL NVARCHAR(1000);
    SET @SQL = 'SELECT ProductID, ProductName, UnitPrice
                FROM Products
                ORDER BY ' + QUOTENAME(@SortColumn) + 
                CASE WHEN @SortDirection = 'DESC' THEN ' DESC' ELSE ' ASC' END +
                ' OFFSET ' + CAST((@PageNumber - 1) * @RowsPerPage AS NVARCHAR) + 
                ' ROWS FETCH NEXT ' + CAST(@RowsPerPage AS NVARCHAR) + ' ROWS ONLY';
    
    -- Execute the paging query
    EXEC sp_executesql @SQL;
END;

Performance Considerations

1. Create indexes to support your ORDER BY clause
2. Be careful with large OFFSET values - performance degrades as OFFSET increases
3. Consider keyset pagination for very large datasets (using WHERE clauses with known boundary values)

Legacy Alternatives

For SQL Server 2008 or earlier, use the ROW_NUMBER() approach:

WITH NumberedRows AS (
    SELECT 
        ProductID, 
        ProductName,
        UnitPrice,
        ROW_NUMBER() OVER (ORDER BY ProductName) AS RowNum
    FROM 
        Products
)
SELECT 
    ProductID, 
    ProductName,
    UnitPrice
FROM 
    NumberedRows
WHERE 
    RowNum BETWEEN (@PageNumber - 1) * @RowsPerPage + 1 
    AND @PageNumber * @RowsPerPage;

OFFSET-FETCH provides a cleaner and more standardized approach to implementing paging in SQL Server, improving both code readability and query performance.