Paging is a common query that developers will use when retrieving results from a table. Often an application may want to display 10 or 20 results, knowing there might be 100 or more. The first request could be using just the top 10, with subsequent requests wanting just 10 results, but from a later part of the entire result set.

While there are a number of ways of coding this, I wanted to examine how the OFFSET and FETCH commands in T-SQL in this article.

The Problem Defined

One of the features I've been asked for many times while building applications is a way to page through the results of a query. This is often needed in some search function where too many results are displayed to put on one page. For an example, imagine that I have a set of orders that are linked to customers. My application wants to get a list of orders for a customer, which returns over 100 results. I want to show these results 10 at a time on the screen.

As an example, this query returns 199 rows in my sample database.

SELECT
                o.OrderID,
                o.OrderDate,
                o.OrderTotal
 FROM
                dbo.Orders   AS o
     INNER JOIN dbo.Customer AS c
         ON c.CustomerID = o.CustomerID
 WHERE          c.CustomerName = 'Emdudamentor International'
 ORDER BY o.OrderDate DESC

The goal is to return rows 1-10, or 11-20, or 21-30, etc. While I could do this in the application, this results in a lot of data transfer to the client and memory usage on the client to manage the paging. This is also more complex code. Instead, I'd like to return only 10 rows to the client

This used to be a very complex process for a developer to build, but it has become much easier with the addition of the OFFSET T-SQL option that was introduced in SQL Server 2012.

OFFSET behavior

The OFFSET command works by skipping some of the rows in a query result. This just shifts the results returned, though all need to be queried. This is part of the ORDER BY clause added at the end of a query. After the columns in the ORDER BY, we use the OFFSET command and then specify the number of rows to skip and then include the ROWS word. Here is a short example. I'll create this table and insert some data:

CREATE TABLE dbo.Counter
 ( CountID INT
 , CountName VARCHAR(20)
 )
GO
INSERT dbo.Counter (CountID, CountName) 
   VALUES
   (1, 'One'),
   (2, 'Two'),
   (3, 'Three'),
   (4, 'Four'),
   (5, 'Five'),
   (6, 'Six')

Let's now query this table. I can do a simple query with an ORDER BY the CountID and I get 6 rows back.

If I add the OFFSET clause, I can remove the first two rows by specifying that number. This means my result set gives me the last 4 rows, skipping two.

I can repeat this with a 4 and see two rows.

Note that I am getting all the results from the table here, and just removing a few from the top, offsetting my results.

The OFFSET command is often joined with the FETCH command, which determines how many rows to return. FETCH is used after the ROWS keyword. In this clause, you specify either FIRST or NEXT (they are the same) and then a number of rows. Again, you need ROWS and the ONLY keyword.  In my sample test, if I want only 3 rows, I'd see this with an OFFSET of 2.

You can read more about the documentation on the ORDER BY BOL page.

Getting Results in Pages

Now that we know how OFFSET works, let's get a few pages of results. The first page is offset 0 rows, which is a valid clause, so we can do this:

SELECT
                o.OrderID,
                o.OrderDate,
                o.OrderTotal
 FROM
                dbo.Orders   AS o
     INNER JOIN dbo.Customer AS c
         ON c.CustomerID = o.CustomerID
 WHERE          c.CustomerName = 'Emdudamentor International'
 ORDER BY o.OrderDate DESC
 OFFSET 0 ROWS
 FETCH NEXT 10 ROWS ONLY;

This gives me sample results up to order 5208 on 2015-12-13.

To get the next page, we would want to offset 10 rows, and use this query:

SELECT
                o.OrderID,
                o.OrderDate,
                o.OrderTotal
 FROM
                dbo.Orders   AS o
     INNER JOIN dbo.Customer AS c
         ON c.CustomerID = o.CustomerID
 WHERE          c.CustomerName = 'Emdudamentor International'
 ORDER BY o.OrderDate DESC
 OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY;

This gives me the first order of 2015-12-25, which is the next order after 52804 shown above, in reverse date order. This is exactly what we want.

To generalize this, we really want ((page number -1) * 10) for page 2. Page 1 here would calculate 0. Page 2 calculates an offset of 10. This is best described with a set of variables for the page and page size, which we can put into the query:

DECLARE
    @page INT = 1,
    @size INT = 10;
SELECT
                o.OrderID,
                o.OrderDate,
                o.OrderTotal
 FROM
                dbo.Orders   AS o
     INNER JOIN dbo.Customer AS c
         ON c.CustomerID = o.CustomerID
 WHERE          c.CustomerName = 'Emdudamentor International'
 ORDER BY       o.OrderDate DESC OFFSET ((@page - 1) * @size) ROWS FETCH NEXT @size ROWS ONLY;

Now we can use these variables as part of our script. This is better expressed as a stored procedure, though certainly you can use variables in constructing a SQL statement from code. Just be sure that you parameterize your statement to prevent SQL Injection.

Performance Considerations

One thing to note is that SQL Server isn't caching this query and then finding the next page of results when you resubmit this. Certainly the data is cached in the buffer pool from the table, but not the query results. This means that each call to a query using OFFSET and FETCH must re-run the query and determine what rows to return. While this is a fairly efficient query, you still need to have indexes on the columns and cover as much of the query as you can.

Aaron Bertrand wrote a very nice review of the performance of this command that looks at some optimizations for fetch. One thing to do is perhaps limit the query early to the rows you need and then use that to get the rest of the data. Using Aaron's technique, I'd rewrite my query like this:

DECLARE
    @page INT = 1,
    @size INT = 10;
;WITH cte (OrderID)
AS (   SELECT          o.OrderID
        FROM
                       dbo.Orders   AS o
            INNER JOIN dbo.Customer AS c
                ON c.CustomerID = o.CustomerID
        WHERE          c.CustomerName = 'Emdudamentor International'
        ORDER BY       o.OrderDate DESC OFFSET ((@page - 1) * @size) ROWS FETCH NEXT @size ROWS ONLY)
 SELECT
                 o.OrderID,
                 o.OrderDate,
                 o.OrderTotal
  FROM
                 dbo.Orders AS o
      INNER JOIN cte        AS c2
          ON c2.OrderID = o.OrderID;

This way I would try to get the clustered index keys I in the CTE using FETCH, which reads less data from some index and then get the data from the Orders table later, joining back to my limited result set.

Conclusion

If you need to do paging in your application, and most of you likely do, then you should take a look at the OFFSET command in SQL Server 2012+ as a way to structure your code. You do need to ensure you have good indexes on your search terms, and certainly still want to limit results where you can. This is a good way to do paging on a server.

If you have the capability on a client, especially a thick client application, you are probably better off doing paging here for smaller sets of data.