Been tinkering with getting a nice paging algorithm out. To get a basic hang of the problem, do take a look at
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:
Should allow sorting
Should not impose any requirements on the table schema/ resultset.
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.
Ideally, should not require dynamic queries. (Though note that this conflicts with 1 & 3 as these two requirements almost make dynamic queries mandatory).
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