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
- OFFSET-FETCH requires an ORDER BY clause
- 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;
- Create indexes to support your ORDER BY clause
- Be careful with large OFFSET values - performance degrades as OFFSET increases
- 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.