title: SQL Server - Speed up count on large table
body: <p>I have a table with close to <strong>30 million</strong> records. Just several columns. One of the column <code>'Born'</code> have not more than <strong>30 different values</strong> and there is an index defined on it. I need to be able to filter on that column and efficiently page through results. </p>
  
  <p>For now I have (example if the year I'm searching for is '1970' - it is a parameter in my stored procedure):</p>
  
  <pre><code>WITH PersonSubset as
  (
      SELECT *, ROW_NUMBER() OVER (ORDER BY Born asc) AS Row
      FROM Person WITH (INDEX(IX_Person_Born)) 
      WHERE Born = '1970'
  )
  SELECT *, (SELECT count(*) FROM PersonSubset) AS TotalPeople
  FROM PersonSubset
  WHERE Row BETWEEN 0 AND 30
  </code></pre>
  
  <p>Every query of that sort (only <code>Born</code> parameter used) returns just over 1 million results.
  I've noticed the biggest overhead is on the count used to return the total results. If I remove <code>(SELECT count(*) FROM PersonSubset) AS TotalPeople</code> from the select clause the whole thing speeds up a lot.</p>
  
  <p>Is there a way to speed up the count in that query. What I care about is to have the paged results returned and the total count.</p>