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