For an application to browse a large dataset, you need to set a limit to the number of rows returned. As users page up and down through the results, the application needs to return the next block of results. This can be done with a Select with a Subquery. The Subquery have virtually no performance penalties over a straight query. The advantage is that you can use the TOP function to get the next block in the subquery, but return the results in a different order.
The key concepts here are:
1. Save the identifiers to the first and last row of the block for pagination.
2. To get the last or previous block, toggle the ASC/DESC on each element of the original order by.
3. To get the previous block, toggle the booleans <> in the Where Clause.
Examples of 30 row page blocks sorted by LastName, FirstName and SysID (to force uniqueness)
Last Page
SELECT * FROM (
SELECT TOP (30) LastName, FirstName, SysID
FROM YourTable
ORDER BY LastName DESC, FirstName Desc, SysID Desc) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC
First Page
SELECT * FROM (
SELECT TOP (30) LastName, FirstName, SysID
FROM YourTable
ORDER BY LastName ASC, FirstName ASC, SysID ASC) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC
Next Page
DECLARE @HighLastName varchar(30) = 'MidLN',
@HighFirstName varchar(30) = 'MidFN',
@HighSysID int = 1000;
SELECT * FROM (
SELECT TOP (30) LastName, FirstName, SysID
FROM YourTable
WHERE LastName > @HighLastName
OR (LastName = @HighLastName AND FirstName > @HighFirstName)
OR (LastName = @HighLastName AND FirstName = @HighFirstName AND SysID > @HighSysID)
ORDER BY LastName ASC, FirstName ASC, SysID ASC) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC
Previous Page
DECLARE @LowLastName varchar(30) = 'MidLN',
@LowFirstName varchar(30) = 'MidFN',
@LowSysID int = 1000;
SELECT * FROM (
SELECT TOP (30) LastName, FirstName, SysID
FROM YourTable
WHERE LastName < @LowLastName
OR (LastName = @LowLastName AND FirstName < @LowFirstName)
OR (LastName = @LowLastName AND FirstName = @LowFirstName AND SysID < @LowSysID)
ORDER BY LastName DESC, FirstName DESC, SysID DESC) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC
Summary
While you could certainly reduce these statements in verboseness, the optimization on the backend is negligible.
This patterns allows for a generic pagination function to be developed with nirtualy no added overhead.