title: Which is the best way to perform pagination on SQL Server?
body: <p>I have a database with more than <strong>2 million</strong> records and I need to perform a pagination to show on my web application, which must have 10 records per page in a <code>DataGrid</code>.</p>
  
  <p>I already tryied to use <code>ROW_NUMBER()</code>, but this way will select all the 2 million records and then get only 10 records. I also tryied to use <code>TOP 10</code>, but I would have to save the first and last id to control the pages. And I've read that using <code>DataAdapter.Fill()</code> will select all the content and then get the 10 records that I need.</p>
  
  <p>Which is the best way? Should I use <code>DataAdapter.Fill()</code>? Or use the SQL Server's function <code>ROW_NUMBER()</code>? Or try to use <code>TOP 10</code>?</p>