The objective of this SQL Server tutorial is to teach you how to use the OFFSET FETCH clause combination with the ORDER BY clause for pagination.
What is Pagination?
A table might consist of thousands or even millions of tuples. It is not possible to access (by way of displaying and viewing) so much information or even a part of it at the same time in one screen or page. The solution to the problem is pagination. Pagination is the process of breaking up a large volume or chunk of information into pages so that they can be accessed and processed one page at a time.
What is OFFSET FETCH clause combination in SQL Server?
The OFFSET FETCH clause combination can only and only be used in a SELECT statement with the ORDER BY clause. It cannot be used otherwise.
Operation
The OFFSET clause is the mandatory clause in the combination and follows the ORDER BY clause. The FETCH clause is optional and follows the OFFSET clause. Both the clauses take the number of rows as an argument. The number of rows specified with the OFFSET clause indicates the number of rows that must be skipped in the resultset or the number of rows after which the resultset should begin. The number of rows specified with FETCH indicates the number of rows that must be returned or displayed in the resultset.
SQL Server OFFSET FETCH Syntax
The basic syntax of SQL Server OFFSET FETCH clause combination is as follows.
SELECT expressions
FROM tables
[ORDER BY expressions ASC|DESC]
[OFFSET offset_row_count ROW|ROWS]
[FETCH FIRST|NEXT fetch_row_count ROW|ROWS ONLY]
In this syntax,
expressions – expressions defined here the column(s) or calculation you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
tables – one or more than one table from where you want to retrieve data.
ORDER BY – clause used to sort the values in a column in either ascending or descending order.
ASC|DESC – keyword used to specify how the values of the column specified by ORDER BY should be sorted. Default is ASC (i.e. ascending) and hence does not need to be explicitly specified.
OFFSET – clause used to specify the number of rows that must be skipped in the resultset or the number of rows after which the resultset should begin. Offset literally means to ignore or cancel out.
offset_row_count – a non-negative integer value specifying the number of rows to be skipped in the resultset. It can be specified directly or as a variable or as an expression (usually a subquery) which returns a non-negative integer value.
ROW|ROWS – both ROW and ROWS are synonyms (i.e. mean the same thing). It simply means that the value preceding it is the number of rows. Both the keywords are permissible and supported for compatibility and interoperability reasons.
FETCH – clause used to specify the number of rows that must be returned or displayed in the resultset.
FIRST|NEXT – both FIRST and NEXT are synonyms (i.e. mean the same thing). It basically means the first set of rows or next set of rows after the offset. Both the keywords are permissible and supported for compatibility and interoperability reasons.
fetch_row_count – a non-negative integer value specifying the number of rows to be returned or displayed in the resultset. It can be specified directly or as a variable or as an expression (usually a subquery) which returns a non-negative integer value.
ONLY – mandatory keyword with FETCH which indicates that only the number of rows specified in the fetch_row_count should be returned in the resultset.
SQL Server OFFSET FETCH Examples
NOTE:
Columns are also referred to as fields or attributes and the terms are used interchangeably.
A row of information in a table is called a tuple.
We will see the different usage scenarios for the OFFSET FETCH clause combination using an example table. Let us consider the case of a music store which has a table called music_genre that stores information about the stock available of the different music genres.
genreid
genre
stock
1
Pop
555
2
Rock
496
3
Rythm & Blues
125
4
Rap
317
5
Country
205
6
Gospel
111
7
Hip-Hop
153
8
Jazz
274
9
Movie Soundtrack
399
10
Heavy Metal
515
11
Grunge
102
12
Ballads
162
music_genre Table (Containing stock data of the different music genres)
1) SQL Server OFFSET FETCH – OFFSET without FETCH example
As it has been mentioned it is possible to use OFFSET without FETCH in which case the query will return all rows in the resultset after skipping the number of rows specified as offset. To understand the same first we run a simple select query which orders the output by stock available.
SELECT *
FROM music_genres
ORDER BY stock;
It produces the below output. Note the sorting in ascending order on the stock column. We will use this resultset as a reference to understand the output of the following examples.
genreid
genre
stock
11
Grunge
102
6
Gospel
111
3
Rythm & Blues
125
7
Hip-Hop
153
12
Ballads
162
5
Country
205
8
Jazz
274
4
Rap
317
9
Movie Soundtrack
399
2
Rock
496
10
Heavy Metal
515
1
Pop
555
Now we will apply offset in the query to skip 5 rows.
SELECT *
FROM music_genres
ORDER BY stock
OFFSET 5 ROWS;
The query will generate the following output. Note that the first 5 rows from the previous resultset has been removed.
genreid
genre
stock
5
Country
205
8
Jazz
274
4
Rap
317
9
Movie Soundtrack
399
2
Rock
496
10
Heavy Metal
515
1
Pop
555
2) SQL Server OFFSET FETCH – OFFSET with FETCH example
We can modify the above query by adding the FETCH clause as below with an argument 5 to indicate that only the next 5 rows after the first 5 rows in the resultset should be returned.
SELECT *
FROM music_genres
ORDER BY stock
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;
The result will be the below output which is almost the same as above leaving out the last 2 rows and displaying only 5 rows after the offset in the resultset.
genreid
genre
stock
5
Country
205
8
Jazz
274
4
Rap
317
9
Movie Soundtrack
399
2
Rock
496
It is also possible to specify the number of rows for both OFFSET and FETCH as a mathematical calculation as below. Here I have used ROW (instead of ROWS) and FIRST (instead of NEXT) to demonstrate their use as discussed above.
SELECT *
FROM music_genres
ORDER BY stock
OFFSET 12-6 ROW
FETCH FIRST 3+2 ROW ONLY;
The above query will generate the below result. It leaves out the first 6 rows (12-6=6) and returns the next 5 rows (3+2=5) from the original ORDER BY resultset above.
genreid
genre
stock
8
Jazz
274
4
Rap
317
9
Movie Soundtrack
399
2
Rock
496
10
Heavy Metal
515
3) SQL Server OFFSET FETCH – row count derived from expression example
The below query is an example where the number the rows to skip (i.e. the offset_row_count) is specified by a subquery with the OFFSET clause.
SELECT *
FROM music_genres
ORDER BY stock
OFFSET (SELECT COUNT(*) FROM music_genres) - 8 ROWS
FETCH NEXT 4 ROWS ONLY;
The query will generate the following output. Here the subquery returns the count of the total number of rows in the table i.e. 12. This is deducted from 8 (12-8=4) and the resultset skips the first 4 rows and returns the next 4 rows from the original ORDER BY resultset above.
genreid
genre
stock
12
Ballads
162
5
Country
205
8
Jazz
274
4
Rap
317
4) SQL Server OFFSET FETCH – row count specified as variable example
It is also possible to use variables which can be reused instead of specifying a constant for the offset_row_count and the fetch_row_count. The same is done when querying the DB through code because it should be possible to run the code as and when required (by varying the variables).
Below is an example of the same. Here we have first declared 2 variables. The first one @startrow indicates the start of the resultset and @rowsperpage indicates the number of rows that will be displayed in each page. The variable declaration syntax is that the variable name will be preceded by @ and followed by datatype and then an optional initial value. Here the datatype is INT and the initial value is 6 and 5 respectively. The query that follows using the variables is the same as the above queries. Only difference is that the offset_row_count is @StartRow – 1 (i.e. 6-1=5) and the fetch_row_count is @RowsPerPage (i.e. 5).
DECLARE @StartRow INT=6;
DECLARE @RowsPerPage INT=5;
SELECT *
FROM music_genre
ORDER BY stock
OFFSET @StartRow - 1 ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;
It produces the below result leaving out the first 5 rows and returning the next 5 rows from the original ORDER BY resultset.