SQL Server 2012: Pagination with Order By and Offset Fetch

Many times developers need to implement pagination on search results. Pagination is the process of dividing the results of a query into discrete numbered pages.

SQL Server 2012 has introduced a new and easy method to implement pagination using OFFSET and FETCH NEXT. This process is actually faster compared to previous complex methods like using row_number. This feature is somewhat similar to the MySQL Limit/Offset clause.

OFFSET: specifies the number of rows to skip before it starts returning rows
FETCH NEXT: the number of rows to display in the result

Let us explore Order By Offset fetch in SQL Server 2012 with examples.

Create a table called COMPANY, and populate it with some data.

IF OBJECT_ID('COMPANY') IS NOT NULL
DROP TABLE COMPANY
GO

CREATE TABLE COMPANY
(
ID        INT PRIMARY KEY,
NAME      VARCHAR(25),
LOCATION  VARCHAR(25)
)
GO

INSERT INTO COMPANY
VALUES (1,'HCL','London'),
       (2,'HP','Bangalore'),
       (3,'Microsoft','Bangalore'),
       (4,'Infosys','Pune'),
       (5,'Google','London'),
       (6,'GE', 'London'),
       (7,'AltiSource','New York'),
       (8,'Facebook','Palo alto'),
       (9,'IBM','New York'),
       (10,'TCS','Mumbai')
GO

SELECT * FROM COMPANY
GO

Problem 1.1 – Using only OFFSET

SELECT   ID, NAME, LOCATION
FROM     COMPANY
ORDER BY ID
OFFSET   3 ROWS

In the query above, we are using only OFFSET, so it will skip the first three rows and will return all remaining rows in a determined order.

Problem 1.2 – Skip zero rows, and fetch the first five rows.

SELECT     ID, NAME, LOCATION
FROM       COMPANY
ORDER BY   ID
OFFSET     0 ROWS
FETCH NEXT 5 ROWS ONLY

In the query above, OFFSET 0 ROWS means we have skipped zero and FETCH NEXT 5 intends to retrieve the next five rows.

Problem 1.3 – Skip the first five rows, and fetch the next five rows.

SELECT     ID, NAME, LOCATION
FROM       COMPANY
ORDER BY   ID
OFFSET     5 ROWS
FETCH NEXT 5 ROWS ONLY

Here, we are skipping the first five rows and fetching the next five rows.

The result above can be achieved using SQL Server 2005/2008 using row_number and derived table.

SELECT ID, NAME, LOCATION
FROM
(
SELECT ID, NAME, LOCATION, ROW_NUMBER() OVER(ORDER BY ID) as rownum
FROM   COMPANY c
) DT
WHERE  DT.rownum BETWEEN 6 AND 10

Performance comparison between OFFSET FETCH and ROW_NUMBER

The OFFSET FETCH approach took 0.003294, and the row_number approach took 0.0033038. This shows the newer approach OFFSET FETCH in SQL Server 2012 is faster.

Problem 1.4 – Using Variables with OFFSET and FETCH

DECLARE @OffSetRows AS INT = 5
DECLARE @FetchRows AS INT = 5

SELECT     ID, NAME, LOCATION
FROM       COMPANY
ORDER BY   ID
OFFSET     @OffSetRows ROWS
FETCH NEXT @FetchRows ROWS ONLY

This is the same as Problem 1.3, but here we are using variables to store OFFSET and FETCH values.