Home » SQL Server Basics » SQL Server OFFSET FETCH

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: