Pagination in SQL Server using OFFSET/FETCH

Pagination is often used in applications where the user can click Previous/Next to navigate the pages that make up the results, or click on a page number to go directly to a specific page.

When running queries in SQL Server, you can paginate the results by using the OFFSET and FETCH arguments of the ORDER BY clause. These arguments were introduced in SQL Server 2012, therefore you can use this technique if you have SQL Server 2012 or higher.

In this context, pagination is where you divide the query results into smaller chunks, each chunk continuing where the previous finished. For example, if a query returns 1000 rows, you could paginate them so that they’re returned in groups of 100. An application can pass the page number and page size to SQL Server, and SQL Server can then use it to return just the data for the requested page.

Example 1 – No Pagination

First, let’s run a query that returns all rows in a table:

SELECT *
FROM Genres
ORDER BY GenreId;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 1         | Rock    |
| 2         | Jazz    |
| 3         | Country |
| 4         | Pop     |
| 5         | Blues   |
| 6         | Hip Hop |
| 7         | Rap     |
| 8         | Punk    |
+-----------+---------+

This example uses no pagination – all results are displayed.

This result set is so small that it wouldn’t normally require pagination, but for the purposes of this article, let’s paginate it.

Example 2 – Display the First 3 Results

This example displays the first three results:

SELECT *
FROM Genres
ORDER BY GenreId
  OFFSET 0 ROWS
  FETCH NEXT 3 ROWS ONLY;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 1         | Rock    |
| 2         | Jazz    |
| 3         | Country |
+-----------+---------+

In this case, I specify that the results should start at the first result and display the next three rows. This is done using the following:

OFFSET 0 ROWS specifies that there should be no offset (an offset of zero).
FETCH NEXT 3 ROWS ONLY gets the next three rows from the offset. Since I specified an offset of zero, the first three rows are fetched.

If all we wanted was the top 3 results, we could have achieved the same result by using the TOP clause instead of specifying the offset and fetch values. However, this wouldn’t have allowed us to do the next part.

Example 3 – Display the Next 3 Results

Now let’s display the next three results:

SELECT *
FROM Genres
ORDER BY GenreId
  OFFSET 3 ROWS
  FETCH NEXT 3 ROWS ONLY;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 4         | Pop     |
| 5         | Blues   |
| 6         | Hip Hop |
+-----------+---------+

So the only thing I changed was the offset.

The offset and fetching values can also be an expression provided as a variable, parameter, or constant scalar subquery. When a subquery is used, it cannot reference any columns defined in the outer query scope (it can’t be correlated with the outer query).

The following examples use expressions to show two approaches to paginating the results.

Example 4 – Pagination by Row Number

This example uses expressions to specify the row number to start at.

DECLARE 
  @StartRow int = 1,
  @RowsPerPage int = 3;
  
SELECT *  
FROM Genres
ORDER BY GenreId ASC
    OFFSET @StartRow - 1 ROWS
    FETCH NEXT @RowsPerPage ROWS ONLY;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 1         | Rock    |
| 2         | Jazz    |
| 3         | Country |
+-----------+---------+

Here, I use @StartRow int = 1 to specify that the results should start at the first row.

Here’s what happens if I increment that value to 2.

DECLARE 
  @StartRow int = 2,
  @RowsPerPage int = 3;
  
SELECT *  
FROM Genres
ORDER BY GenreId ASC
    OFFSET @StartRow - 1 ROWS
    FETCH NEXT @RowsPerPage ROWS ONLY;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 2         | Jazz    |
| 3         | Country |
| 4         | Pop     |
+-----------+---------+

It starts at the second row. Using this method, I can specify the exact row to start at.

Example 5 – Pagination by Page Number

This example is almost identical to the previous example, except that it allows you to specify the page number, as opposed to the row number.

DECLARE 
  @PageNumber int = 1,
  @RowsPerPage int = 3;
  
SELECT *  
FROM Genres
ORDER BY GenreId ASC
    OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
    FETCH NEXT @RowsPerPage ROWS ONLY;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 1         | Rock    |
| 2         | Jazz    |
| 3         | Country |
+-----------+---------+

So the first result is the same. However, let’s see what happens when we increment @PageNumber to 2 (I renamed this variable to reflect its new purpose).

DECLARE 
  @PageNumber int = 2,
  @RowsPerPage int = 3;
  
SELECT *  
FROM Genres
ORDER BY GenreId ASC
    OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
    FETCH NEXT @RowsPerPage ROWS ONLY;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 4         | Pop     |
| 5         | Blues   |
| 6         | Hip Hop |
+-----------+---------+

This time the results start at the fourth row. So using this method you can simply pass the page number rather than the row number.

Example 6 – Pagination Loop

To finish off, here’s a quick example that loops through all pages and specifies the starting row number for each iteration:

DECLARE 
  @StartRow int = 1, 
  @RowsPerPage int = 3;
WHILE (SELECT COUNT(*) FROM Genres) >= @StartRow  
BEGIN
    SELECT *  
    FROM Genres 
    ORDER BY GenreId ASC   
        OFFSET @StartRow - 1 ROWS   
        FETCH NEXT @RowsPerPage ROWS ONLY;
SET @StartRow = @StartRow + @RowsPerPage;  
CONTINUE
END;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 1         | Rock    |
| 2         | Jazz    |
| 3         | Country |
+-----------+---------+
(3 rows affected)
+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 4         | Pop     |
| 5         | Blues   |
| 6         | Hip Hop |
+-----------+---------+
(3 rows affected)
+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 7         | Rap     |
| 8         | Punk    |
+-----------+---------+
(2 rows affected)

Example 7 – ROW vs ROWS

If you encounter code that uses ROW instead of ROWS, both arguments do the same thing. They are synonyms and are provided for ANSI compatibility.

Here’s the first example on this page, but with ROW instead of ROWS.

SELECT *
FROM Genres
ORDER BY GenreId
  OFFSET 0 ROW
  FETCH NEXT 3 ROW ONLY;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 1         | Rock    |
| 2         | Jazz    |
| 3         | Country |
+-----------+---------+

Example 8 – FIRST vs NEXT

The same applies to FIRST and NEXT. These are synonyms provided for ANSI compatibility.

Here’s the previous example but with FIRST instead of NEXT.

SELECT *
FROM Genres
ORDER BY GenreId
  OFFSET 0 ROW
  FETCH FIRST 3 ROW ONLY;

Result:

+-----------+---------+
| GenreId   | Genre   |
|-----------+---------|
| 1         | Rock    |
| 2         | Jazz    |
| 3         | Country |
+-----------+---------+