title: Best stored procedure for paging in SQL server version 2008 in a large data?
body: <p>I have ASP.NET website which performance is very important. This website works but with low performance. I have to increase performance of this project. </p>
  
  <p>First I see paging in the grids in the report of this project. I have to say database of this project is SQL Server 2000 and I used temp table for paging, but I think with this stored procedure. I fetch all records then I chose 10 rows of this result and I think it's not efficient. Because it fetches 50000 rows but choses 10 of them. </p>
  
  <p>I want to just get time 10 rows each time. We want to change SQL Server version to 2008 and I want to use paging methods in the 2008 version. I searched a lot. I find this stored procedure but I think it's same as my first query; fetch all rows then use row number. </p>
  
  <p>I want to know what's the best stored procedure for paging in SQL Server 2008 in a large database? Any tips or tricks would be welcome. Thanks in advance.</p>
  
  <p>This is my first paging method :</p>
  
  <pre><code>USE AdventureWorks
  GO
  DECLARE  @StartRow INT
  DECLARE  @EndRow INT
      SET    @StartRow = 120
      SET @EndRow = 140
  
  
  CREATE TABLE #tables (RowNumber INT IDENTITY(1,1),
                  FirstName VARCHAR(100), LastName VARCHAR(100),
                  EmailAddress VARCHAR(100))
  INSERT INTO #tables (FirstName, LastName, EmailAddress)
      SELECT    PC.FirstName, PC.LastName, PC.EmailAddress
      FROM    Person.Contact PC
      ORDER BY FirstName, LastName, EmailAddress
  
  SELECT    FirstName, LastName, EmailAddress
      FROM    #tables
      WHERE    RowNumber &gt; @StartRow
          AND RowNumber &lt; @EndRow
  DROP TABLE #tables
  </code></pre>
  
  <p>and this second in SQL Server 2008:</p>
  
  <pre><code>SELECT * FROM ( 
       SELECT 
            ROW_NUMBER() OVER (ORDER BY fdate) AS row, * 
       FROM somthing 
  ) AS a WHERE row BETWEEN 1 AND 10
  </code></pre>