What is the most efficient way to page large amounts of data in SQL Server 2000?

If I have a query with a lot of information (something like a couple of views that each hit a handful of tables, with many tables having tens of thousands of rows), and I just need to get 10 records from it to display to the user, what's开发者_运维问答 the best way, performance-wise, to retrieve those records while still supporting SQL Server 2000? Once I can use SQL Server 2005, ROW_NUMBER seems like the obvious choice (correct me if I'm wrong), but what to do in 2000?

Greg Hamilton has an article which uses SET ROWCOUNT and SELECTing into a variable to avoid having to reference rows that aren't needed, with some pretty compelling performance results. However, MSDN says

If a variable is referenced in a select list, it should be assigned a scalar value or the SELECT statement should only return one row.

But then it goes on to say

Note that effects are only visible if there are references among the assignments.

If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set.

Indicating that it's really okay in this instance (right?)

Greg ends up with this:

CREATE  PROCEDURE [dbo].[usp_PageResults_NAI] 
(
    @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.*, d.name as DepartmentName 
FROM employees e
   INNER JOIN Departments D ON
       e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO 

This method assumes that you have a unique ID to order by, I don't think that you can use this method as-is when sorting on, say, a non-unique DateTime column.