Ranking has improvement considerably from SQL Server 2000 to SQL Server 2005/2008 to SQL Server 2012.
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 (https://blog.sqlauthority.com)
