How can I do server side pagination of results in SQL Server 2000?

Question

In SQL Server 2005, there is a feature called row_number() which makes pagination very simple.

SELECT * FROM table WHERE row_number() between x and y

Is there any SQL server way to do the same thing in SQL Server 2000?

(Note: I don't have access to a unique sequential numbering for the query, i.e. something which would be a synonym for row_number())

Answer 1

Not sure if this is the most elegant solution, but it worked for me when we used SQL 2000...

If you're writing a stored procedure, you can do it with a temporary table.

Create a temporary table which has an automatically incrementing Identity column as well as the same columns as your result set.

Then, select the data from the result set and insert it into this temporary table (in the right order).

Finally, return results from your temporary table where the value of your Identity column acts as your row number.

Answer 2

SELECT  *
FROM    (
        SELECT  TOP (Y - X ) *
        FROM    (
                SELECT  TOP Y  *
                FROM    mytable
                ORDER BY
                        column
                ) q
        ORDER BY
                column DESC
        )
ORDER BY
        column

Answer 3

You can also use cursor for this.

DECLARE @i INT
DECLARE C CURSOR FOR
SELECT ... FROM ... ORDER BY ...

OPEN C
FETCH ABSOLUTE @StartRow FROM C
SET @i = 1
WHILE (@@FETCH_STATUS == 0) AND (@i < @EndRow - @StartRow) BEGIN
   -- Do whatever you need
   FETCH NEXT FROM C
END
CLOSE C
DEALLOCATE C

The only problem here is that each row is returned as a separate result set, but it does the job.