OFFSET and FETCH allow you to skip a number of rows and limit the number of rows returned.

Basic syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
OFFSET number_of_rows_to_skip ROWS
FETCH NEXT number_of_rows_to_fetch ROWS ONLY;
  • OFFSET n ROWS → Skip the first n rows.
  • FETCH NEXT m ROWS ONLY → Return the next m rows.
  • ORDER BY is required when using OFFSET FETCH.

Step 1: Example table

Assume the Employees table:

EmployeeID FirstName LastName Department Salary
1 John Doe IT 5000
2 Jane Smith HR 4500
3 Mike Brown IT 6000
4 Sara White HR 4700
5 Tom Green IT 5200

Step 2: Retrieve first 2 rows

SELECT EmployeeID, FirstName, Salary
FROM Employees
ORDER BY Salary ASC
OFFSET 0 ROWS
FETCH NEXT 2 ROWS ONLY;

Result:

EmployeeID FirstName Salary
2 Jane 4500
4 Sara 4700

OFFSET 0 ROWS means start from the first row, then fetch 2 rows.

Step 3: Skip first 2 rows and get next 2

SELECT EmployeeID, FirstName, Salary
FROM Employees
ORDER BY Salary ASC
OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY;

Result:

EmployeeID FirstName Salary
1 John 5000
5 Tom 5200

OFFSET 2 ROWS skips the first 2 lowest salaries.

Step 4: Using only OFFSET

You can also skip rows without limiting:

SELECT EmployeeID, FirstName, Salary
FROM Employees
ORDER BY Salary ASC
OFFSET 3 ROWS;

Result: (skips first 3 rows, returns the rest)

EmployeeID FirstName Salary
5 Tom 5200
3 Mike 6000

FETCH is optional; if omitted, all remaining rows after OFFSET are returned.

Step 5: Using OFFSET FETCH for pagination

Example: Page size = 2 rows, Page 2

DECLARE @PageNumber INT = 2;
DECLARE @PageSize INT = 2;SELECT EmployeeID, FirstName, Salary
FROM Employees
ORDER BY Salary ASC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Explanation:

  • Page 1: OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY → rows 1–2
  • Page 2: OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY → rows 3–4
  • Page 3: OFFSET 4 ROWS FETCH NEXT 2 ROWS ONLY → row 5

Categorized in:

SQL Server,