Paging in SQL Server 2012 vs. previous versions

Question

I have used lot of paging code in SQL Server 2008. The format is

With CTE(
 -- SQL With RowNumber
)

-- SELECT ALL Page N

This is working fine. But I have heard that SQL Server 2012 has better paging support. So, if I change all my stored procedures with paging to the new method, will my queries be more efficient?

Answer 1

Actually, the OFFSET/FETCH extension is more syntactic sugar than performance enhancement. I've heard of a few cases where performance can be improved, but in all of my tests thus far, the performance is equivalent to the CTE with ROW_NUMBER() that you're already using - or at least not "better enough" to justify a code change. IMHO. You'll want to actually test your queries in your environment against your data to see if the performance benefit is worth the refactor.

Some very early performance comparisons here:

• https://sqlblog.org/2010/11/10/sql-server-v-next-denali-using-the-offset-clause-paging

And from an updated article in 2015:

• https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch

And the documentation:

• http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx