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
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.