title: Select batch of rows SQL Server 2008
body: <p>I'm trying to retrieve a batch of rows and have achieved this using the row_number function, to for example retrieve the first 10,000 rows as follows:</p>
  
  <pre><code>select * 
  from 
      (select 
           row_number() over (order by id) as row_num, * 
       from 
           forms) as batch
  where 
      batch.row_num between 1 and 10000
  </code></pre>
  
  <p>This performs well first the first batch but becomes progressively slower as it reaches higher row numbers and one of the tables I'm using has > 2.7 million rows. </p>
  
  <p>Is there any way to do this with a linear performance? </p>
  
  <hr>
  
  <p>There's a clustered index on the primary key column and 5 other non-unique, non-clustered on some of the other columns. I'm processing the whole table in batches sequentially and have currently chosen 10,000 as the batch size. The 1-10,000 query takes less than a second but, for example, 200,000 - 210,000 takes 10 seconds and by the time it's in the millions it's > 1 minute. </p>