TSQL 2012 – OFFSET and FETCH
April 4, 2012 Leave a Comment
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.
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.