Programmer's Rules

SQL Server

OFFSET-FETCH option in SQL Server

Description of a flexible extension to the ORDER BY clause in SQL Server

SQL Serverjob interview70-761

The OFFSET-FETCH option filters requested rows. It depends on ORDER BY clause. Its main purpose is to provide a mechanism for paging through results.

The functionality of OFFSET-FETCH depends on specifying the number of rows to skip and number of rows to select. In other words, one should show the staring point (offset) and provide quantity of rows to return (a fetch value)

Let's take into consideration the following query:

which result is:

Price
1
2
3
3
4

OFFSET-FETCH option can be used in a shorter version, with OFFSET keyword only:

resulting in:

Price
3
3
4

As you can see the first 2 rows are not taken into consideration and all other rows are returned.

When using OFFSET with FETCH you can define the number of rows to return:

Now the result is:

Price
3
3

To enable a more natural syntax it is possible to use ROW instead of ROWS and FIRST instead of NEXT.
Tutorial statistics