Sharing of thoughts and information is what blogging is all about. This way we can learn from each other. Post A Comment!These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Chris Di LulloSr. IT Pro Marketing Manager Twitter | LinkedIn Pierre Roman Twitter | LinkedIn Mitch Garvis Twitter | LinkedIn Anthony Bartolo Twitter | LinkedIn
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),
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');
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
EXECUTING THE ABOVE STATEMENT WILL RETURN THE BELOW RESULT SET AS REQUESTED: (Note: ITEM_ID values are being generated thru Identity column)
(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
OFFSET @OFFSET ROWS
FETCH NEXT @FETCH ROWS ONLY
EXECUTING THE ABOVE COMMAND WILL RETURN THE SAME RESULTS AS USING CONSTANT VALUE WITH OFFSET AND FETCH.