paging in Database using Sqlserver

SQL SERVER – Tricks for Row Offset and Paging in Various Versions of SQL Server

Paging is one of the most needed tasks when developers are developing applications. SQL Server has introduced various features of SQL Server 2000 to the latest version of SQL Server 2012. Here is the blog post which I wrote which demonstrates how SQL Server Row Offset and Paging works in various versions of the SQL Server. Instead of giving the generic algorithm, I have used AdventureWorks database and build a script. This will give you better control over your data if you have installed the AdventureWorks database and you can play around with various parameters.

The goal is to retrieve row number 51 to 60 from the table Sales.SalesOrderDetails of database AdventureWorks.

You can install the AdventureWorks database and you can run following queries based on your version.

USE AdventureWorks2012
GO--------------------------------------------------
-- SQL Server 2012
--------------------------------------------------DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6SELECT SalesOrderDetailID, SalesOrderID, ProductIDFROM Sales.SalesOrderDetailORDER BY SalesOrderDetailID
OFFSET (@PageNumber-1)*@RowsPerPage ROWSFETCH NEXT @RowsPerPage ROWS ONLYGO--------------------------------------------------
-- SQL Server 2008 / R2
-- SQL Server 2005
--------------------------------------------------DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6SELECT SalesOrderDetailID, SalesOrderID, ProductIDFROM (SELECT SalesOrderDetailID, SalesOrderID, ProductID,ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNumFROM Sales.SalesOrderDetail ) AS SODWHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1AND @RowsPerPage*(@PageNumber)GO--------------------------------------------------
-- SQL Server 2000
--------------------------------------------------DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6SELECT SalesOrderDetailID, SalesOrderID, ProductIDFROM(SELECT TOP (@RowsPerPage)SalesOrderDetailID, SalesOrderID, ProductIDFROM(SELECT TOP ((@PageNumber)*@RowsPerPage)SalesOrderDetailID, SalesOrderID, ProductIDFROM Sales.SalesOrderDetailORDER BY SalesOrderDetailID) AS SODORDER BY SalesOrderDetailID DESC) AS SOD2ORDER BY SalesOrderDetailID ASCGO