Almost all applications require end-users to search through information. Search results are often shown in some sort of list controls such as Grid, Listview etc. It is very often the case that the search results contain too many records to show in one page. In those cases, search results are shown one page after another, each page containing fixed number of records.
Various solutions have been tried by solution developers. They include creating temporary tables that contain the result set to caching the result set in middle-tier for faster access. There are pros and cons to each approach which I will not delving into.
This article explains another way to do pagination. This mechanism leaves most of the work to RDBMS query execution engine. It works like this.
Here is an example. Let's take the example of Employees table in Northwind database. This table contains following fields (Data types are not included for brevity)
EmployeeId (PK), LastName, FirstName, Title, BirthDate and other fields.
Let's say that we want to search employees by last name. The result sets should be ordered by last name.
Here is the initial query. I am assuming the page size is 20.
Select Top 20 LastName, FirstName, Title
Where LastName like @searchpattern --passed as input
Order By LastName
After executing this, presentation layer store the values for LastName from the first row and last row in its state. This will be used in the processing of Previous and Next commands.
When user wants to see the previous page, presentation layer sends a query to SQL that returns a page of matched records. Search predicate should be modified as follows
Where Clause := LastName like @lastname and LastName < <Value of last name from the first row>
Now the query looks like this
Where LastName like @searchpattern and LastName < @lastnamefromfirstrow --passed as input
Processing 'Next' is very similar to previous. The search predicate would look like
Where clause := LastName like @lastname and LastName > <Value of last name from the last row>
Where LastName like @searchpattern and LastName > @lastnamefromlastrow --passed as input
Are we done? Not quite. These queries will work only if the LastName is unique, which we all know is not the case. So what should we do?
In these cases, we should include additional fields that make each record in the result set unique. For example, adding EmployeeId as part of projection would guarantee uniqueness of record in the result set and order the result sets based on LastName followed by EmployeeId.
Here are the modified queries
Select Top 20 EmployeeId, LastName, FirstName, Title
Where LastName like @lastname --passed as input
Order By LastName, EmployeeId
Where LastName like @searchpattern and
( (LastName < @lastnamefromfirstrow) or
((LastName=@lastnamefromfirstrow) and (EmployeeId < @empidfromfirstrow))
This makes sure that even if the LastName is not unique, the previous query returns correct results. Next query is very similar to previous query.
You can see that the query gets complicated due to uniqueness issue. But, it is easy to come up with a general form to frame queries appropriately. Also, one should create appropriate indices to make sure that the performance doesn't suffer. Stored procedures can also be used for faster processing.
Issues with this approach
Main issue with this approach is that there are certain scenarios where things get difficult. For example, if you want to go to LastPage, or page <n>, then you have to stroll through one page after another to go to the page. There are ways to optimize this, but it needs little bit of additional coding and of course, there is a definite perf cost associated with this.
But if your scenarios support only Prev and Next primitives, then this would work just fine.