SQL Server 2012 introduces brand new query hints that allow you to implement query paging solution. In the past, we have used TOP operator to return the top number of rows from a table. However, OFFSET & FETCH options of the ORDER BY clause can give you more benefits than just the TOP operator.

 

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, you can now prevent using cursors for such queries and it’s also much easier to write as well.

 

So let’s take a look at what OFFSET and FETCH means:

 

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.

 

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

 

Let’s take an example of querying 5 rows from a table that has 10 records. To make this an interesting example, lets query 5 records from a 10 records table starting from the 4TH record:

 

In a real-life scenario, you’ll have millions of records and you may want to query rows from a specific offset. So back to the above example:

 

Please pay attention to the OFFSET and FETCH clauses below to understand how and where to place them in a query.

 

LETS CREATE AN ITEMS TABLE

CREATE TABLE ITEMS

(

ITEM_ID INT IDENTITY(1,1),

ITEM_NAME VARCHAR(50)

);

GO;

 

INSERT ROWS IN ITEMS TABLE

INSERT INTO ITEMS VALUES ('MANGO');

INSERT INTO ITEMS VALUES ('APPLE');

INSERT INTO ITEMS VALUES ('BANANA');

INSERT INTO ITEMS VALUES ('KIWI');

INSERT INTO ITEMS VALUES ('PLUMS');

INSERT INTO ITEMS VALUES ('GRAPES');

INSERT INTO ITEMS VALUES ('WATERMELON');

INSERT INTO ITEMS VALUES ('HONEYDEW');

INSERT INTO ITEMS VALUES ('CHERRY');

INSERT INTO ITEMS VALUES ('STRAWBERRY');

GO;

 

QUERYING THE TABLE USING OFFSET AND FETCH

--IN THIS QUERY, WE ARE OFFSETTING/SKIPPING THE ROWS BY 3 RECORDS (starting at 4th record) 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: (Note: ITEM_ID values are being generated thru Identity column)

 

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.

 

HERE’S AN EXAMPLE:

 

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

 

 

DamirB-BlogSignature