SQL-OFFSET and FETCH Clause

In this tutorials, we will learn how to use SQL OFFSET and FETCH clauses and how make simple pagination using these clause.

SQL Server OFFSET and FETCH

OFFSET and FETCH Clause are used with SELECT and ORDER BY clauses to limit the number of rows returned by a query.

1. OFFSET - The OFFSET clause specifies the number of rows to skip before starting to return rows from the query. OFFSET can only be used with ORDER BY clause. OFFSET value must be greater than or equal to zero. It cannot be negative, else return error.
2. FETCH - It is an optional clause that provides the number of rows we want to fetch or return after the OFFSET in a result set or query. We cannot use it without OFFSET. OFFSET value must be greater than or equal to zero. It cannot be negative, else return error.

The following illustrates the OFFSET and FETCH clauses:

OffSET Example

The below query use OFFSET 3 ROWS that means it skip 3 rows from starting.

SELECT StudentID, studentname, Marks, Section
FROM students ORDER BY StudentID
OFFSET 3 ROWS  

Output

FETCH Example

The below query use FETCH 2 ROWS that means it return number of rows after the OFFSET in a result set.

SELECT StudentID, studentname, Marks, Section
FROM students ORDER BY StudentID
OFFSET 3 ROWS 
FETCH NEXT 2 ROWS ONLY; 

Output

Pagination in SQL Server using Offset and Fetch

SQL Server offers new query hints that allow you to implement query paging solution. In the past, we’ve used TOP operator to return the top number of rows from a table, however, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.

Traditionally you would use a cursor to implement this solution however with the introduction of OFFSET and FETCH its much easier.

The below query defines the pagignation in simple way:

DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 1
SET @RowspPage = 3 
SELECT StudentID, StudentName
FROM students
ORDER BY StudentID
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY; 

The above query return first 3 records from table.