SQL SERVER – Server Side Paging in SQL Server 2011 – A Better Alternative
- by pinaldave
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)
Filed under: Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
