Very efficient paging in SQL Server 2000 or 2005 without temp table

What I mean by paging in SQL Server is something like this. Assume you have a table that has a million rows. You want to show the user only a portion of those records such as through a web interface. The user could then click next page / previous page to see more results. You may also show the user a list of pages so they can jump to any page without clicking next/previous links. With that said, most solutions for SQL paging stored procedures involve a temp table for SQL 2000 and using ROW_NUMBER() function in SQL 2005. While the SQL 2005 solution is pretty easy it doesn't work in SQL 2000. The articules imply that this solution is better from a performance standpoint, I found it to be the opposite, so your mileage may vary. I recommend if you have SQL 2005, then use the ROW_NUMBER() function as the code clean, and easy to understand. Below is a very technical solution, but it is not difficult to adapt to your situation, and it works on both 2000 and 2005. I am not going to try to explain it here as it is explained in great detail here: http://www.4guysfromrolla.com/webtech/042606-1.shtml. BTW, thank you to author for this solution. The math required to convert a zero-based page index to a value that can be used for the @startRowIndex parameter is as follows, and could be in your .net code, or whatever. int startRowIndex = Math.Ceiling(pageIndex * pageSize); // NOTE: you will need to do some data type conversions, but this is basically it. The parameter @startRowIndex is 1 based. The parameter @maximumRows is essentially how many records you want in a page. CREATE PROCEDURE [dbo].[GetEmployeesPaged] ( @startRowIndex int, @maximumRows int ) AS DECLARE @first_id int, @startRow int -- A check can be added to make sure @startRowIndex isn't > count(1) -- from employees before doing any actual work unless it is guaranteed -- the caller won't do that -- Get the first employeeID for our page of records SET ROWCOUNT @startRowIndex SELECT @first_id = employeeID FROM employees ORDER BY employeeid -- Now, set the row count to MaximumRows and get -- all records >= @first_id SET ROWCOUNT @maximumRows SELECT e.Name, e.EmpoyeeID FROM employees e WHERE employeeid >= @first_id ORDER BY e.EmployeeID SET ROWCOUNT 0 GO The solution above is the best performance wise for a SQL 2000 database. I recommend if you have SQL 2005 that you use the ROW_NUMBER() function as shown below. CREATE PROCEDURE [dbo].[GetEmployeesPaged2005] ( @startRowIndex int, @maximumRows int ) AS -- A check can be added to make sure @startRowIndex isn't > count(1) -- from employees before doing any actual work unless it is guaranteed -- the caller won't do that SELECT * FROM (SELECT e.Name, e.EmpoyeeID ROW_NUMBER() OVER(ORDER BY employeeid) as RowNum FROM employees e ) as DerivedTableName WHERE RowNum BETWEEN @startRowIndex AND (@maximumRows + @startRowIndex - 1) SET ROWCOUNT 0 GO