Been tinkering with getting a nice paging algor…

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s