Paging is a crucial task for developers when building applications. It allows users to view data in smaller, manageable chunks rather than loading all the data at once. SQL Server offers various features for paging, starting from SQL Server 2000 to the latest version, SQL Server 2012.

In this blog post, we will explore how SQL Server Row Offset and Paging work in different versions of SQL Server. Instead of providing a generic algorithm, we will use the AdventureWorks database and provide a script that you can run and experiment with different parameters.

Our goal is to retrieve rows 51 to 60 from the “SalesOrderDetails” table in the AdventureWorks database. To achieve this, you can install the AdventureWorks database and run the following queries based on your SQL Server version:

SQL Server 2012

USE AdventureWorks2012

DECLARE @RowsPerPage INT = 10
DECLARE @PageNumber INT = 6

SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

SQL Server 2008 / R2 and SQL Server 2005

DECLARE @RowsPerPage INT = 10
DECLARE @PageNumber INT = 6

SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
    SELECT SalesOrderDetailID, SalesOrderID, ProductID, ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
    FROM Sales.SalesOrderDetail
) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber - 1) * @RowsPerPage) + 1 AND @RowsPerPage * @PageNumber

SQL Server 2000

DECLARE @RowsPerPage INT = 10
DECLARE @PageNumber INT = 6

SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
    SELECT TOP (@RowsPerPage) SalesOrderDetailID, SalesOrderID, ProductID
    FROM (
        SELECT TOP ((@PageNumber) * @RowsPerPage) SalesOrderDetailID, SalesOrderID, ProductID
        FROM Sales.SalesOrderDetail
        ORDER BY SalesOrderDetailID
    ) AS SOD
    ORDER BY SalesOrderDetailID DESC
) AS SOD2
ORDER BY SalesOrderDetailID ASC

These examples demonstrate different approaches to achieve paging in SQL Server. While there may be more efficient ways to accomplish this task, the techniques shown above are effective and can be easily implemented.

If you have any suggestions or alternative methods for achieving paging in SQL Server, please feel free to leave a comment.