Effective server side paging in SQL Server 2000

Recently a client asked me to implement paging on the administrators side of a CV/online application submission system. This essentially lists all the current applications in the system and other data (time of submission, personal details etc). The system has been well received and little thought was paid when developing it in order to scale it up in the long term, hence this webpage wasn’t necessarily as performant as it could be.

The table is implemented as a ASP.net repeater bound to Gentle.NET objects. The repeater itself doesnt have any inbuilt paging.

I could have used the PagedDataSource and hook it up to my collection of applications but the problem here is that the SQL command invoked by Gentle.NET would still return a full data set (the main bottleneck, currently stands at around 5000 applicants) which would be post-filtered. Instead, I wanted a server side solution, actually, a SQL Server 2000 side solution. It seems implementing server side paging in SQL Server 2005 is a bit easier with the use of the ROW_NUMBER function (http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx)

Anyway, I came across this ingenious solution for SQL Server 2000 which doesn’t require Stored Procedures or the construction of a temporary table at http://www.themanaged.net/archive/2007/02/13/10.aspx, which solved my problems through effective use of the TOP command, and whats more is robust to filtering/sorting, ordering and joins!