sql-serverpagination

Paging results from a complex SQL Server query

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?

Solution

  • 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.