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.
Follow the reactions below and share your own thoughts.