Pagination in SQL Server using Offset and Fetch

SQL Server offers new query hints that allow you to implement query paging solution. In the past, we’ve used TOP operator to return the top number of rows from a table, however, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.

Let’s assume you have 50,000 records in a table and you want to query 1200 rows starting from 25000. Traditionally you would use a cursor to implement this solution however with the introduction of OFFSET and FETCH its much easier.

OFFSET and FETCH when used together can return the exact set of data you are looking for.

OFFSET: Allows you to offset the record pointer to a specific row in a table

FETCH: Allows you to fetch/return number of rows you request in Fetch.

Lets take an example of querying rows from a table that has 50,000 records. Lets query 1200 records starting from 25,000 thousand record.

Step 1 – Create a table

CREATE TABLE ITEMS
(
  ITEM_ID INT IDENTITY(1,1),
  ITEM_NAME VARCHAR(50)
);

Step 2 – Populate the table with data

INSERT INTO ITEMS VALUES 
      ('MANGO'),
      ('APPLE'),
      ('BANANA'),
      ('KIWI'),
      ('PLUMS'),
      ('GRAPES'),
      ('WATERMELON'),
      ('HONEYDEW'),
      ('CHERRY'),
      ('STRAWBERRY');

Step 3 – Query the table with Offset and Fetch

--IN THIS QUERY, WE ARE OFFSETTING/SKIPPING THE ROWS BY 3 RECORDS AND RETURNING THE NEXT 5 ROWS.
SELECT * FROM ITEMS
  ORDER BY ITEM_ID
  OFFSET 3 ROWS
  FETCH NEXT 5 ROWS ONLY
GO
 
--EXECUTING THE ABOVE STATEMENT WILL RETURN THE BELOW RESULT SET AS REQUESTED:
 
ITEM_ID	  ITEM_NAME
4	       KIWI
5	       PLUMS
6	       GRAPES
7	       WATERMELON
8	       HONEYDEW
 
(5 row(s) affected)
 
--NOTE: YOU CAN ALSO USE VARIABLES WITH OFFSET AND FETCH CLAUSES.
 
DECLARE @OFFSET INT=3, @FETCH INT=5
SELECT * FROM ITEMS ORDER BY ITEM_ID
  OFFSET @OFFSET ROWS
  FETCH NEXT @FETCH ROWS ONLY
GO
 
--EXECUTING THE ABOVE COMMAND WILL RETURN THE SAME RESULTS AS THE USING CONSTANT VALUE WITH OFFSET AND FETCH.
 
ITEM_ID	  ITEM_NAME
4	       KIWI
5	       PLUMS
6	       GRAPES
7	       WATERMELON
8	       HONEYDEW
 
(5 row(s) affected)