TSQL 2012 - OFFSET and FETCH

Its about time, MySQL has had a feature LIMIT which is similar to the usage of OFFSET and FETCH for years. Now in SQL Server 2012 Microsoft introduces OFFSET and FETCH.

The OFFSET and FETCH functionality is used to achieve data paging server side in TSQL. Thing of a page like your typical search results where you are shown the first 20 results, then you click to page 2 where you see the next 20 search results. Over the years of coding in different languages I have seen a lot of very creative solutions for data paging, most of which have been extremely difficult to follow and most of which have been difficult to maintain. Most of these are done client side in ASP, .net, PHP or some other web scripting language where if you want rows 21 to 40 you select all the rows, then skip over the first 20, then process rows 21 to 40 and eventually give up on the result set loop at row 40. It works, but it s not pretty.

Prior to SQL Server 2012, my favorite way to do server side paging was using a Common Table Expression (CTE), which I have presented at a couple of SQL Saturdays now.

With SQL Server 2012 the OFFSET and FETCH options are introduced into the SELECT statement. The OFFSET statement tells you where to start and the FETCH statement tells how many rows you want to grab.

For this sample, I will be using the same Revenue table from a previous TSQL 2012 example.

First, just look at the Revenue table to see whats there

SELECT * 
FROM REVENUE
ORDER BY Year, DepartmentID ASC;

Now to grab the second 10 rows with a CTE, the old way of doing it prior to FETCH and OFFSET.

WITH RevenuePagingCTE AS
(
 SELECT *,
 ROW_NUMBER() OVER (ORDER BY Year, DepartmentID ASC) as RowNum
 FROM REVENUE
)
SELECT DepartmentID, Revenue, Year
 FROM RevenuePagingCTE
 WHERE RowNum > 10
 AND RowNum <= 20;

And now for the new OFFSET and FETCH option which will produce the same output as the CTE, but is much easier to put together.


SELECT DepartmentID, Revenue, Year
 FROM REVENUE
 ORDER BY Year, DepartmentID ASC
 OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY;

So how do these compare performance wise. Is the CTE faster, or is the FETCH/OFFSET option faster? Lets take a look at the execution plan. As you can see in this example although the CTE has more steps that it goes through, the total cost between the two are equal at 50% each.

So overall, FETCH and OFFSET doesn’t give you anything that you couldn’t do before, it just makes it much easier, and faster.

TSQL 2012 – OFFSET and FETCH

Leave a Reply Cancel reply