Sql-server – Paging in SQL Server 2012 vs. previous versions

pagingsql serversql-server-2012

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?

Best Answer

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

Related Solutions

Sql-server – SQL Server 2000 to 2008 to 2012

The best answer to this question is a bit of a short one, but go look at the books online articles that talk about what features are due to be removed in future versions and are no longer there in various versions.

For instance.. In SQL Server 2008 the "old-style" left outer join syntax (WHERE Table2.col1 *= Table1.Col1) is listed as deprecated (there but on the way out in a future version) and it is finally gone in SQL Server 2012. So if you kept that syntax in 2008 it would work, but would require work after upgrading to SQL 2012 or greater or you would have a failure.

To get you started, check out these links that take you to the articles that describe what features are no longer supported in the listed version and what features are due to be removed in the future.. Then look for these in your code.. I would also say you may want to look at future compatibility beyond SQL Server 2012 if you are holding off 3+ years.. For instance TEXT fields will still work in SQL Server 2012, but they won't likely work in the version out after SQL Server 2012 according to the links below.

SQL Server 2012 Deprecated Feature List
SQL Server 2008R2 Deprecated Feature List

You should also make sure that you let your database run in SQL Server 2008 compatibility mode after your upgrade to 2008 and ensure it works well in 2008 and to allow you to upgrade it to 2012 - SQL Server 2000 Databases, or databases operating in SQL Server 2000 compatibility mode will not migrate to SQL Server 2012.

I also wrote an answer that talks extensively about upgrades and migrations and general preparedness steps.

SQL Server – How Paging Works with ROW_NUMBER

An alternative to test might be:

;WITH x AS (SELECT EmpID, k = ROW_NUMBER() OVER (ORDER BY EmpID) FROM dbo.Emp)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
WHERE x.k BETWEEN (((@Index - 1) * @PageSize) + 1) AND @Index * @PageSize
ORDER BY ...;

Yes, you hit the table twice, but in the CTE where you scan the whole table you are only grabbing the key, not ALL of the data. But you really should look at this article:

http://www.sqlservercentral.com/articles/T-SQL/66030/

And the follow-up discussion:

http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx

In SQL Server 2012 of course you can use the new OFFSET / FETCH NEXT syntax:

;WITH x AS 
(
  SELECT EmpID FROM dbo.Emp
    ORDER BY EmpID
    OFFSET  @PageSize * (@Index - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
ORDER BY ...;

I also blogged about this in more detail here:

Best Answer

Related Solutions

Sql-server – SQL Server 2000 to 2008 to 2012

SQL Server – How Paging Works with ROW_NUMBER

Related Question