SQL Server 2012 : OFFSET FETCH VS CTE WITH ROW_NUMBER()

 

Since SQL Server 2005 and 2008 ROW_NUMBER() Function available and many of us will us that function with CTE to get data by custom page size.

In SQL Server 2012 there is new functionality to get record by page size and it is known as OFFSET FETCH.

While Learning SQL Server 2012 Syntax for OFFSET and FETCH clause I found that this will not work if you are specify order by clause in Select query. So I decided to check ROW_NUMBER() with CTE to new functionality in terms of performance.

For this example AdventureWorks_2012 database in used.

Old Way Of Query ( Version 2005 , 2008)

WITH CTE
AS
(
Select AccountNumber , P.FirstName , P.LastName , P.MiddleName , ROW_NUMBER() OVER (ORDER BY P.BusinessEntityId) RowNum  from Sales.Customer SC
LEFT JOIN Person.Person P ON SC.PersonID = P.BusinessEntityID
WHERE AccountNumber Like 'A%'
)
Select AccountNumber , FirstName , LastName , MiddleName from CTE WHERE RowNum > 1000 and RowNum <= 2000

SQL Server 2012

Select AccountNumber , P.FirstName , P.LastName , P.MiddleName   from Sales.Customer SC
LEFT JOIN Person.Person P ON SC.PersonID = P.BusinessEntityID
WHERE AccountNumber Like 'A%'
ORDER BY P.BusinessEntityID
OFFSET 1000 ROWS
FETCH NEXT 100 Rows ONLY

At this point I thought there must be a performance improvement in SQL Server 2012. To check that I used execution plan and statics IO.

Execution Plan ( Both Query Took 50%)

STATISTICS IO ( Both are same)

As per my thinking there is no performance improvement and it is just syntax get change ( Internally In SQL Engine both query are same as they generated same execution plan)

Please give me your opinion regarding this and correct me If I am wrong somewhere to understand functionality.