What is a good way to paginate out of SQL 2000 using Start and Length parameters?

Question

I have been given the task of refactoring an existing stored procedure so that the results are paginated. The SQL server is SQL 2000 so I can't use the ROW_NUMBER method of pagination. The Stored proc is already fairly complex, building chunks of a large sql statement together before doing an sp_executesql and has various sorting options available.

The first result out of google seems like a good method but I think the example is wrong in that the 2nd sort needs to be reversed and the case when the start is less than the pagelength breaks down. The 2nd example on that page also seems like a good method but the SP is taking a pageNumber rather than the start record. And the whole temp table thing seems like it would be a performance drain.

I am making progress going down this path but it seems slow and confusing and I am having to do quite a bit of REPLACE methods on the Sort order to get it to come out right.

Are there any other easier techniques I am missing?

Answer 1

No, I'm afraid not - SQL Server 2000 doesn't have any of the 2005 niceties like Common Table Expression (CTE) and such..... the method described in the Google link seems to be one way to go.

Marc

Answer 2

Also take a look here http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

scroll down to Stored Procedure Methods

Answer 3

Depending on your application architecture (and your amount of data, it's structure, DB server load etc.) you could use the DB access layer for paging.

For example, with ADO you can define a page size on the record set (DataSet in ADO.NET) object and do the paging on the client. Classic ADO even lets you use a server side cursor, though I don't know if that scales well (I think this was removed altogether in ADO.NET).

MSDN documentation: Paging Through a Query Result (ADO.NET)

Answer 4

After playing with this for a while there seems to be only one way of really doing this (using Start and Length parameters) and that's with the temp table.

My final solution was to not use the @start parameter and instead use a @page parameter and then use the

    SET @sql = @sql + N'

    SELECT * FROM
    (
    SELECT TOP ' + Cast( @length as varchar) + N' * FROM 
            (
            SELECT TOP ' + Cast( @page*@length as varchar) + N'
                 field1,
                 field2 
                 From Table1
                 order by field1 ASC
            )  as Result   
            Order by Field1 DESC
     )  as Result
     Order by Field 1 ASC'

The original query was much more complex than what is shown here and the order by was ordered on at least 3 fields and determined by a long CASE clause, requiring me to use a series of REPLACE functions to get the fields in the right order.

Answer 5

We've been using variations on this query for a number of years. This example gives items 50,000 to 50,300.

select top 300 
  Items.*
from Items
where 
  Items.CustomerId = 1234 AND
  Items.Active = 1 AND
  Items.Id not in
  ( 
    select top 50000 Items.Id
    from Items
    where 
      Items.CustomerId = 1234 AND
      Items.Active = 1
    order by Items.id
  )
order by Items.Id

Answer 6

There are two SQL Server 2000 compliant answers in this StackOverflow question - skip the accepted one, which is 2005-only: