Execute the following T-SQL scripts in Query Analyzer or Management Studio Query Editor to demonstrate paging a large result sets. The inner most query (CTE, select from select, subselect, derived table x) can be replaced with desired query.

-- SQL paged results - paging results using T-SQL - sql paging row_number
-- SQL applying ROW_NUMBER and CTE for paging results - sql set rowcount
DECLARE @LinesPerPage int,
@PageNumber int,
@StartRow int
SET @LinesPerPage = 40
SET @PageNumber =10
SET @StartRow = @PageNumber * @LinesPerPage
 
;WITH CTE AS
(SELECT I.CustomerID,
        C.FirstName,
        C.LastName,
        A.AddressLine1,
        A.City,
        SP.Name                                        AS State,
        CR.Name                                        AS Country,
        ROW_NUMBER()
          OVER(ORDER BY SP.Name, A.City, I.CustomerID) AS RN
FROM   AdventureWorks.Person.Contact AS C
       INNER JOIN AdventureWorks.Sales.Individual AS I
         ON C.ContactID = I.ContactID
       INNER JOIN AdventureWorks.Sales.CustomerAddress AS CA
         ON CA.CustomerID = I.CustomerID
       INNER JOIN AdventureWorks.Person.Address AS A
         ON A.AddressID = CA.AddressID
       INNER JOIN AdventureWorks.Person.StateProvince SP
         ON SP.StateProvinceID = A.StateProvinceID
       INNER JOIN Person.CountryRegion CR
         ON CR.CountryRegionCode = SP.CountryRegionCode )
 
SELECT TOP (@LinesPerPage) * FROM CTE
WHERE RN >= @StartRow
ORDER by RN

-

-----------

-- SQL paging - Paging recordset - Paging large datasets -- Paging T-SQL

USE Northwind;

DECLARE @LinesPerPage int,

            @PageNumber int,

            @StartRow int

SET @LinesPerPage = 40

SET @PageNumber =10

SET @StartRow = @PageNumber * @LinesPerPage

SELECT * FROM

      (SELECT TOP (@LinesPerPage) * FROM

            (SELECT TOP (@StartRow) *

            FROM [Order Details]

            WHERE UnitPrice > $10.0

            ORDER BY 1 ) x

      ORDER by 1 DESC) y

ORDER by 1
GO

-- RESULTS

-----------