OFFSET and FETCH Clause in SQL Server

In this article, we will learn about the concept of the Fetch() function.

The OFFSET and FETCH clauses are the options of the ORDER BY clause. They allow you to limit the number of rows to be returned by a Query .

OFFSET :

This clause is used to specify the beginning point for returning rows from a result set. Basically, it ignores the initial set of records. SQL Server can use it only with the ORDER BY clause. If its value is negative, an error will be returned. Therefore, it should always be greater than or equal to zero.

The general form for the OFFSET argument is:

SELECT   columns
FROM     table
ORDER BY columns OFFSET rows-to-skip ROWS

For example,

SELECT   NationalIDNumber,
         JobTitle,
         HireDate
FROM     HumanResources.Employee
ORDER BY HireDate
         OFFSET 10 ROWS

1. OFFSET is part of the ORDER BY clause. It cannot be used on its own.
2. OFFSET values must be zero or greater. A negative number results in an error.
3. When OFFSET is 0, then no rows are skipped.
4. If OFFSET is greater than the number of rows in the ordered results, then no rows are returned.

FETCH :

It is an optional clause that provides the number of rows we want to return after the OFFSET in a query. We cannot use it without OFFSET. Its value cannot be negative similar to OFFSET. Therefore, it should always be greater than or equal to zero; otherwise, it will throw an error.

For example,

SELECT   NationalIDNumber,
         JobTitle,
         HireDate
FROM     HumanResources.Employee
ORDER BY HireDate
         OFFSET 10 ROWS
         FETCH NEXT 5 ROWS ONLY

1. The OFFSET clause is mandatory while the FETCH clause is optional. Also, the FIRST and NEXT are synonyms respectively so you can use them interchangeably. Similarly, you can use the FIRST and NEXT interchangeably.

Output :