Another paging approach
I have hard time believing that, but this is something that I learned from how MS CRM works. This is the first time that I ever saw this approach, although it seems so obvious when you think about it.
Updated: Fixed a bug found by Nathan Baulch, thanks!
The idea here is to solve the problem of SQL 2000 not really having a good way to do paging. The basic pattern is this:
select top [N] * from [TableName]
where [orderCriteria] > @orderCriteriaPreviousMaxValue or
(
[Id] > @PreviousID and
[orderCriteria] = @orderCriteriaPreviousMaxValue
)
order by [orderCriteria], [Id]
What do I mean by that?
Let us say that I want to get the first page of customers, I would issue the following query:
select top 25 * from Customers
order by Customers.Name, Customers.Id
Now, I want to get the next page, I can do this using this approach:
select top 25 * from Customers
where Customers.Name >= @CustomerNamePreviousMaxValue or
(
Customers.Id > @PreviousId and
Customers.Name > @CustomerNamePreviousMaxValue
)
order by Customers.Name, Customers.Id
All we need to remember is the last row's value from the previous query, and pass it to this one. Using this approach we can efficently page through result sets, without having to deal with the limitations of SQL Server limited top capabilities.
The usage of the filtering and the ordering ensure that we will always get the next result back. I find it quite elegant. What is surprising, I don't think that I have ever seen it in any other place.