Paging result sets in SQL Server 2012 – Offset and Fetch

In SQL Server 2012, additions to the ORDER BY clause – OFFSET and FETCH – allows paging through data sets. I will briefly describe this feature used alongside the WITH clause and consider the performance impact of the tables sort order.

First of all, I think that this is a useful enhancement – I have spent hours coding SQL to provide paging though result sets. Typically, this is to retrieve data for web sites. For example, in a table clustered on AccountID, I ran a query to select accounts based on the parentId and ordered by PageNumber – as shown below.
SELECT AccountID,AccountName, DateModified, AccountType,pageNumber
FROM [dbo].[WebAccounts]
WHERE ParentID  =175
ORDER BY PageNumber OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY

The execution plan (see below) used by the ofset…fetch seems very simple – especially when compared to earlier attempts to page using (for example) row_number . On my example system, the query plan consists of a clustered index scan followed by a sort. Clearly, the sort was the most expensive operation.

If I change the ORDER By to sort on the clustered index (AccountID) then the query performs much faster – running roughly 25% faster than a query where the ORDER BY column does not match the table order.

For example, the following TSQL statements are identical except for the ORDER BY. The first ORDER By reflects the business requirement, but the second reflects the clustering key on the base table.
SELECT AccountID,AccountName, DateModified, AccountType,pageNumber
FROM [dbo].[WebAccounts]
WHERE ParentID  =175
ORDER BY pageNumber OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY

SELECT AccountID,AccountName, DateModified, AccountType,pageNumber
FROM [dbo].[WebAccounts]
WHERE ParentID  =175
ORDER BY AccountID OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY

The query plans (below) reveal a major performance improvement:

The Offset…Fetch construct offers simplicity and speed. If you can design your tables to reflect the ORDER By clause the performance can be speeded up.