How To OFF SET And 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:
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 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 respectively 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 samlpe database for the demonstration.
products
The following query returns all products from the products table and sorts the products by their list prices and names:
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:
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:
SQL Server OFFSET FETCH skip 10 rows fetch next 10 rows example
To get the top 10 most expensive products you use both OFFSET and FETCH clauses:
SQL Server OFFSET FETCH top 10 most expensive products
In this example, the ORDER BY clause sorts the products by their list prices in descending order. Then, the OFFSET clause skips zero row and the FETCH clause fetches the first 10 products from the list.
In this tutorial, you have learned how to use the SQL ServerOFFSET FETCH clauses the limit the number of rows returned by a query.

Post a Comment

0 Comments