Been tinkering with getting a nice paging algorithm out. To get a basic hang of the problem, do take a look at

Many ASP developers create their own search engines that return back the results to the web browser one page at a time

Do take a look at the second query given. I've modified it a little bit so that you can sort by a given field and removed a bit of the cruft (the au_lname like '%A%' bit). Here the table used is called Pager - with a column called Name.

Some of my bare bones requirements for a paging system are:

1. Should allow sorting

2. Should not impose any requirements on the table schema/ resultset.

3. Should be done on SQL Server as much as possible. Definitely not default paging that results in all rows being sent to the middle layer.

4. Ideally, should not require dynamic queries. (Though note that this conflicts with 1 & 3 as these two requirements almost make dynamic queries mandatory).

5. Should not use temp tables.

declare @pagenum int
declare @pageSize int

set rowcount  @pagesize

select * 
   from Pager P
where
    (select count(*)
    from Pager P2
    where P2.Name <= P.name) > @pagesize * @pagenum
order by
       p.name