Execute the following SQL Server T-SQL script in SSMS Query Editor to create result set paging for Sales Orders and Sales Order Details:

-- T-SQL query with CTE and double ROW_NUMBER sequencing

-- SQL Server paging result set

USE AdventureWorks2008;

DECLARE  @PageSize INT,

         @PageNo   INT,

         @FirstRow INT,

         @LastRow  INT

SET @PageSize = 40

SET @PageNo = 100

SET @FirstRow = (@PageNo - 1) * @PageSize + 1

SET @LastRow = @PageSize + (@PageNo - 1) * @PageSize;

WITH cteSalesOrder

     AS (SELECT h.SalesOrderID,

                SalesOrderDetailID,

                ProductID,

                OrderQty,

                UnitPrice,

                UnitPriceDiscount,

                LineTotal,

                ROW_NUMBER()

                  OVER(ORDER BY h.SalesOrderID DESC)   AS RowNo,

                ROW_NUMBER()

                  OVER(PARTITION BY h.SalesOrderID

                  ORDER BY SalesOrderDetailID DESC) AS RowNoDetail

         FROM   Sales.SalesOrderDetail d

                JOIN Sales.SalesOrderHeader h

                  ON h.SalesOrderID = d.SalesOrderID)

SELECT   RowNo,

         RowNoDetail,

         SalesOrderID,

         SalesOrderDetailID,

         ProductID,

         OrderQty,

         UnitPrice,

         UnitPriceDiscount,

         LineTotal

FROM     cteSalesOrder

WHERE    RowNo BETWEEN @FirstRow AND @LastRow

ORDER BY RowNo ASC;

Related article:

SQL Server 2005 Paging Results