SQL 2012 paging performance trick

I will show you a new and improved way to implement paging within t-sql

Tags: Technology, SQL
Posted by mjourdan on August 10, 2014

Included in the release of Microsoft SQL Server 2012, they introduced the OFFSET and FETCH feature. This feature can make paging a breeze and boost paging performance. It's also Micrsoft's recommended paging solution.

In SQL 2005/2008/2010

In SQL 2005/2008/2010 it required a CTE and some work.

DECLARE @Start INT;
DECLARE @End INT;
SELECT @Start = 10, 
       @End = 20;
WITH ClientCTE
     AS (SELECT RowId, 
                MAX(InsertedDate) AS InsertedDate, 
                ROW_NUMBER() OVER(
                ORDER BY MAX(InsertedDate) DESC) AS RowNumber
         FROM MyTable
         GROUP BY RowId)
     SELECT RowId, 
            InsertedDate
     FROM MyCTE
     WHERE RowNumber > @Start
           AND RowNumber <= @End;

Now in SQL 2012/2014

They made it brain dead simple by just adding offset and fetch to the order by clause.

SELECT RowId, 
       InsertedDate
FROM
(
    SELECT RowId, 
           MAX(InsertedDate) AS InsertedDate
    FROM MyTable
    GROUP BY RowId
) SubQueryAlias
ORDER BY InsertedDate DESC
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows