title: Getting a Subset of Records along with Total Record Count
body: <p>I'm working on returning a recordset from SQL Server 2008 to do some pagination. I'm only returning 15 records at a time, but I need to have the total number of matches along with the subset of records. I've used two different queries with mixed results depending on where in the larger group I need to pull the subset. Here's a sample:</p>
  
  <pre><code>SET NOCOUNT ON;
  WITH tempTable AS (
    SELECT
       FirstName
       , LastName
       , ROW_NUMBER() OVER(ORDER BY FirstName ASC) AS RowNumber 
     FROM People
     WHERE 
        Active = 1
  )
  
  SELECT 
     tempTable.*     
     , (SELECT Max(RowNumber) FROM tempTable) AS Records    
  FROM tempTable     
  WHERE
     RowNumber &gt;= 1
     AND RowNumber &lt;= 15
  ORDER BY
     FirstName
  </code></pre>
  
  <p>This query works <em>really</em> fast when I'm returning items on the low end of matches, like records 1 through 15. However, when I start returning records 1000 - 1015, the processing will go from under a second to more than 15 seconds.</p>
  
  <p>So I changed the query to the following instead:</p>
  
  <pre><code>SET NOCOUNT ON;
  WITH tempTable AS (
    SELECT * FROM (
       SELECT
          FirstName
          , LastName
          , ROW_NUMBER() OVER(ORDER BY FirstName ASC) AS RowNumber 
          , COUNT(*) OVER(PARTITION BY NULL) AS Records
        FROM People
        WHERE 
           Active = 1
     ) derived
     WHERE RowNumber &gt;= 1 AND RowNumber &lt;= 15
  )
  
  SELECT 
     tempTable.*     
  FROM tempTable     
  ORDER BY
     FirstName
  </code></pre>
  
  <p>That query runs the high number returns in 2-3 seconds, but also runs the low number queries in 2-3 seconds as well. Because it's doing the count for each of 70,000+ rows, it makes <em>every</em> request take longer instead of just the large row numbers.</p>
  
  <p>So I need to figure out how to get a good row count, as well as only return a subset of items at any point in the resultset without suffering such a huge penalty. I could handle a 2-3 second penalty for the high row numbers, but 15 is too much, and I'm not willing to suffer slow loads on the first few pages a person views.</p>
  
  <p><em>NOTE</em>: I know that I don't need the CTE in the second example, but this is just a simple example. In production I'm doing further joins on the tempTable after I've filtered it down to the 15 rows I need.</p>