SQL Server OFFSET FETCH

Summary: in this tutorial, you will learn how to use the SQL Server OFFSET FETCH clauses to limit the number of rows returned by a query.

Introduction to SQL Server OFFSET FETCH

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

Here’s the syntax for using the OFFSET and FETCH clauses:

ORDER BY column_list [ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLYCode language: SQL (Structured Query Language) (sql)

In this syntax:

• The OFFSET clause specifies the number of rows to skip before starting to return rows from the query. The offset_row_count can be a constant, variable, or parameter that is greater or equal to zero.
• The FETCH clause specifies the number of rows to return after the OFFSET clause has been processed. The offset_row_count can be a constant, variable, or scalar that is greater or equal to one.
• The OFFSET clause is mandatory, while the FETCH clause is optional. Additionally, FIRST and NEXT are synonyms and can be used interchangeably. Similarly, you can use ROW and ROWS interchangeably.

The following picture illustrates the OFFSET and FETCH clauses:

It’s important to note that you must use the OFFSET and FETCH clauses with the ORDER BY clause. Otherwise, you encounter an error.

The OFFSET and FETCH clauses are preferable for implementing the query paging solutions compared to the TOP clause.

The OFFSET and FETCH clauses have been available since SQL Server 2012 (11.x) and later, as well as Azure SQL Database.

SQL Server OFFSET and FETCH clause examples

We will use the products table from the sample database for the demonstration.

1) Using the SQL Server OFFSET FETCH example

The following query uses a SELECT statement to retrieve all rows from the products table and sorts them by the list prices and names:

SELECT
    product_name,
    list_price
FROM
    production.products
ORDER BY
    list_price,
    product_name;Code language: SQL (Structured Query Language) (sql)

Output:

To skip the first 10 products and return the rest, you use the OFFSET clause as shown in the following statement:

SELECT
    product_name,
    list_price
FROM
    production.products
ORDER BY
    list_price,
    product_name 
OFFSET 10 ROWS;Code language: SQL (Structured Query Language) (sql)

Output:

To skip the first 10 products and select the next 10 products, you use both OFFSET and FETCH clauses as follows:

SELECT
    product_name,
    list_price
FROM
    production.products
ORDER BY
    list_price,
    product_name 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY;
Code language: SQL (Structured Query Language) (sql)

Output:

2) Using the OFFSET FETCH clause to get the top N rows

The following example uses the OFFSET FETCH clause to retrieve the top 10 most expensive products from the products table:

SELECT
    product_name,
    list_price
FROM
    production.products
ORDER BY
    list_price DESC,
    product_name 
OFFSET 0 ROWS 
FETCH FIRST 10 ROWS ONLY;Code language: SQL (Structured Query Language) (sql)

Output:

In this example:

• First, the ORDER BY clause sorts the products by their list prices in descending order.
• Then, the OFFSET clause skips zero rows, and the FETCH clause retrieves the first 10 products from the list.

Summary

• Use the SQL Server OFFSET FETCH clauses to limit the number of rows returned by a query.