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.
The following are the syntax that illustrates the use of OFFSET and FETCH clause:
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.
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:
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:
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:
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:
Executing the query will return the desired result:
If we execute the above statement without the OFFSET clause, we will get an error:
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: