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 the options of the ORDER BY clause. They allow you to limit the number of rows to be returned by a query.

The following illustrates the syntax of 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. Also, the FIRST and NEXT are synonyms, so you can use them interchangeably. Similarly, you can use the FIRST and NEXT interchangeably.

The following illustrates the OFFSET and FETCH clauses:

SQL Server OFFSET FETCH

Note that you must use the OFFSET and FETCH clauses with the ORDER BY clause. Otherwise, you will get an error.

The OFFSET and FETCH clauses are preferable for implementing the query paging solution than the TOP clause.

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

SQL Server OFFSET and FETCH examples

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

products

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)
SQL Server OFFSET FETCH result set

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)
SQL Server OFFSET FETCH example

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)
SQL Server OFFSET FETCH skip 10 rows fetch next 10 rows example

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)
SQL Server OFFSET FETCH top 10 most expensive products

In this example:

  • First, the ORDER BY clause sorts the products by the list prices in descending order.
  • Then, the OFFSET clause skips zero row and the FETCH clause fetches 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.
Was this tutorial helpful?