Execute the following script in Query Analyzer or Query Editor to demonstrate paging a large result set. The inner most query (derived table x) can be replaced.

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