Paging results from a complex SQL Server query

Question

What's the best way to page the results from a complex query in SQL Server 2005 and 2008?

I came up with the following approach:

1. Perform the query, save its results to a table variable or temp table.

2. Use the following code:

   DECLARE @Rows INT = (SELECT COUNT(*) FROM @TableVariable)
   
   DELETE TV
   FROM   @TableVariable TV
   JOIN   (SELECT TV2.PrimaryKey, ROW_NUMBER() OVER(...) AS RowNo
           FROM   @TableVariable TV2) N ON TV.PrimaryKey = TV2.PrimaryKey
   WHERE N.RowNo < @FromRow OR N.RowNo > @ToRow
   
   SELECT PrimaryKey, SomeComputedField, ...
   FROM   @TableVariable
   
   RETURN @Rows
   

However, I don't have any experience paging huge datasets myself. What do other SOers have to suggest?

Answer 1

A STORED PROCEDURE body could be written as:

;WITH rownums AS (
    SELECT  tempTable.[link], 
            ROW_NUMBER() OVER (ORDER BY tempTable.[link]) AS rownum 
    FROM    <temptable here> AS tempTable
)
SELECT  tempTable.link
FROM    <temptable here> AS tempTable
        INNER JOIN rownums AS rn
        ON  rn.[link]   =   drn.[link]
WHERE   rn.[rownum] BETWEEN @low AND @high

Then you would need to provide the STORED PROCEDURE with the range (@low and @high) and you would need the temp table of course.

P.S. Also, I think your variable declaration will only work on 2008, if I remember correctly 2005 does not support single line instantiation and initialization.