Sql Server offset 0 usage

 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

SQL

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:

SQL

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 0 ROWS 
FETCH NEXT 5 ROWS ONLY;

How it works:

1. ORDER BY Salary DESC: Sorts the employees from highest to lowest salary.

2. OFFSET 0 ROWS: Starts at the very first row of that sorted list (skips nothing).

3. 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.

SQL

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 BY is Mandatory: You cannot use OFFSET without an ORDER BY clause. The database needs a definitive sorting order to know exactly which rows it is skipping.

• FETCH is Optional: You can use OFFSET without FETCH. If you write OFFSET 10 ROWS without a FETCH clause, SQL Server will skip the first 10 rows and return everything else.

• No mixing with TOP: You cannot use TOP and OFFSET-FETCH in the same query block. OFFSET-FETCH was introduced in SQL Server 2012 specifically as a more robust replacement for using TOP for pagination.

Cheers

Samitha