SQL SERVER – Server Side Paging in SQL Server Denali – A Better Alternative

Ranking has improvement considerably from SQL Server 2000 to SQL Server 2005/2008 to SQL Server 2011.

Here is the blog article where I wrote about SQL Server 2005/2008 paging methodSQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table. One can achieve this using OVER clause and ROW_NUMBER() function.

Now SQL Server 2011 has come up with the new Syntax for paging. Here is how one can easily achieve it.

USE AdventureWorks2008R2
GODECLARE @RowsPerPage INT = 10, @PageNumber INT = 5SELECT *FROM Sales.SalesOrderDetailORDER BY SalesOrderDetailID
OFFSET @PageNumber*@RowsPerPage ROWSFETCH NEXT 10 ROWS ONLYGO

I consider it good enhancement in terms of T-SQL. I am sure many developers are waiting for this feature for long time.

We will consider performance different in future posts.