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 method SQL 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
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET @PageNumber*@RowsPerPage ROWS
FETCH NEXT 10 ROWS ONLY
GO


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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)