title: Paging results from a complex SQL Server query
body: <p>What's the best way to page the results from a complex query in SQL Server 2005 and 2008?</p>
  
  <p>I came up with the following approach:</p>
  
  <ol>
  <li><p>Perform the query, save its results to a table variable or temp table.</p></li>
  <li><p>Use the following code:</p>
  
  <pre><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 &lt; @FromRow OR N.RowNo &gt; @ToRow
  
  SELECT PrimaryKey, SomeComputedField, ...
  FROM   @TableVariable
  
  RETURN @Rows
  </code></pre></li>
  </ol>
  
  <p>However, I don't have any experience paging huge datasets myself. What do other SOers have to suggest?</p>