SQL Server OFFSET FETCH

This article will explain the use of OFFSET FETCH functionality in retrieving a record with limited memory and avoiding an out-of-memory exception. The FETCH and OFFSET clauses in SQL Server are used in combination with the SELECT and ORDER BY clauses to limit the range of records returned by the query. It was first introduced with the SQL Server version 2012 for doing pagination of the result set. It is useful when our database contains a huge amount of data.

Usage of OFFSET and FETCH

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.

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.

Syntax

The following are the syntax that illustrates the use of OFFSET and FETCH clause:

SELECT * FROM table_name
ORDER BY columns [ASC |DESC]
OFFSET no_of_rows_to_skip
FETCH {FIRST | NEXT} no_of_rows_to_return {ROW | ROWS} ONLY

In this syntax, we have defined the table name from which data will be retrieved. Next, we specified the ORDER BY clause for records displayed in ascending or descending order. Next, we specified the OFFSET to skip the number of records, and finally, FETCH is used to return the set of records.

Example

Let us understand the use of OFFSET and FETCH clauses practically. Suppose we have table named customer that contains the following data:

Suppose you want to get the range of records starting from 5 up to the next four rows. We can do this by using the OFFSET and FETCH clauses easily. Here is the query:

SELECT * FROM customer 
ORDER BY c_name 
OFFSET 5 ROWS 
FETCH NEXT 4 ROWS ONLY;

Executing the statement will return the following output where OFFSET 5 skips the five rows and FETCH 4 ROWS ONLY to display the required result.

If we want to skip the first five customer records and return the rest, we only need to use the OFFSET clause as shown in the below query:

SELECT * FROM customer 
ORDER BY c_name 
OFFSET 5 ROWS;

Executing the statement will return the desired output:

If we want to retrieve the top 6 customers based on the ascending order of the customer name, we need to use both OFFSET and FETCH clauses both as shown below:

SELECT * FROM customer 
ORDER BY c_name 
OFFSET 0 ROWS 
FETCH NEXT 6 ROWS ONLY;

Executing the query will return the required result:

If we want to get the last three records of the customer when sorted by their name, we can use the OFFSET and FETCH clause as below:

SELECT c_name, email, state FROM customer ORDER BY c_name 
OFFSET (SELECT COUNT(*) FROM customer) - 3 ROWS
FETCH NEXT 3 ROWS ONLY;

Executing the query will return the desired result:

If we execute the above statement without the OFFSET clause, we will get an error:

SELECT c_name, email, state FROM customer 
ORDER BY c_name 
FETCH NEXT 3 ROWS ONLY;

SQL Server will through the following error message:

Difference between TOP and OFFSET & FETCH Clause

The TOP and OFFSET FETCH clauses are both used to limit the number of rows returned by the query in SQL Server. However, they have some differences that are described in the below table:

TOP	OFFSET & FETCH
The TOP clause can only return the specific records from a query result.	OFFSET and FETCH return the set of records in a query. OFFSET is used to skip the number of rows, and FETCH provides the number of rows we want to return after the OFFSET in the result.
TOP is not ANSI-compliant, so it can only be used with Microsoft products like SQL Server and MS-Access.	OFFSET and FETCH clauses are ANSI-compliant that were recently added to SQL Server 2012.
A TOP clause can be used with or without an ORDER BY in the SELECT statement's column list.	It should always use with the ORDER BY clause.
It cannot return the records from the middle of the results.	It can return the records from the middle of the results.
It supports SQL Server version 2005 and above.	It supports SQL Server version 2012 and above.