In SQL Server, OFFSET 0 ROWS instructs the database to skip exactly zero rows before returning data.
On its own, skipping zero rows doesn't change the output, but when paired with the FETCH NEXT clause, it becomes the standard, ANSI-compliant way to implement pagination (specifically, grabbing the "first page" of results).
Basic Syntax
SELECT column_name(s)
FROM table_name
ORDER BY sort_column
OFFSET 0 ROWS
FETCH NEXT n ROWS ONLY;
e.g 1: The "First Page" of Results
Imagine you have an Employees table, and you are building a UI that displays 5 employees per page, starting with the highest earners.
To get Page 1, you use OFFSET 0:
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;
How it works:
ORDER BY Salary DESC: Sorts the employees from highest to lowest salary.OFFSET 0 ROWS: Starts at the very first row of that sorted list (skips nothing).FETCH NEXT 5 ROWS ONLY: Grabs exactly 5 rows and stops.
(To get Page 2, you would change it to OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY).
e.g 2: Dynamic Pagination in a Stored Procedure
The most common real-world use case for OFFSET 0 is inside stored procedures where pagination math is calculated dynamically using variables.
When a user requests Page 1, the math automatically calculates to OFFSET 0.
DECLARE @PageNumber INT = 1; -- The user wants the 1st page
DECLARE @PageSize INT = 10; -- The user wants 10 records per page
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
ORDER BY LastName ASC
-- The math: (1 - 1) * 10 = 0. So this resolves to OFFSET 0 ROWS
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
Important Rules
ORDER BYis Mandatory: You cannot useOFFSETwithout anORDER BYclause. The database needs a definitive sorting order to know exactly which rows it is skipping.FETCHis Optional: You can useOFFSETwithoutFETCH. If you writeOFFSET 10 ROWSwithout aFETCHclause, SQL Server will skip the first 10 rows and return everything else.No mixing with
TOP: You cannot useTOPandOFFSET-FETCHin the same query block.OFFSET-FETCHwas introduced in SQL Server 2012 specifically as a more robust replacement for usingTOPfor pagination.