Paging in Microsoft SQL Server

Every SQL Server and/or VB developer will come across the need to return data from a database. There will come a time when that database grows to a size when returning the entire list of records is not feasible.

I initially encountered the problem when widespread use of the Internet involved using a 28.8k or 56k modem to connect to the Internet. Most users were not specifying a filter and therefore returning the entire list of records. I had to think of a fix.


Next course of action, search the Internet. Plenty of articles about paging using the recordset's AbsolutePage property. After all the coding and testing internally it seemed to have worked. Now was the time to test it over the modem. No luck. It seems that paging only works when building recordset on a single table. My application was using a stored procedure to join about 8 tables. It took me a while to find that out with some write-and-test code sessions.

Then I got an idea. I could manually have the database do the work, but only request the specified pages I needed. It was fairly simple too. I simply allowed the user to submit their query, and instead of returning the records I would return the recordcount.

The first stored procedure would create a temporary table with an IDENTITY column, and a key column which linked the data. Then, if I requested page #1 (and if i specified each page was 50 records) i would simply write a query which filtered IDENTITY >=1 and IDENTITY <=50.

This would then be joined to the original query, but would only return the 50 records requested. The datagrid displaying the rows would get populated with the first page. In code if I detected the user going past that first page, the application would fetch the second page of data and fill the grid. Eventually, the user would scroll down to the bottom - at which point the entire grid was populated and no need for trips to the database.

Although this solution has its drawbacks. You have to create a global temporary table that each user accesses, and they won't be able to open multiple versions of the application (different filter, same table, screwy results). Then you have to worry about deleting the tables when they log out.

I only recently saw a second solution that was implemented by a colleage of mine in a financial application. It was a very complicated version of what I am going to show you now.

select * from (
select top @records_per_page * from (
select top @records_per_page * @page_number * from vwRECORDS
order by date_posted desc) as foo
order by date_posted asc) as bar
order by date_posted desc

If you follow the example on a dataset of 10 records and a pagesize=2 you can very easily follow what it is doing.

Ingenious isn't it? I'm sure this can be adopted to any product that has a similar convention to the "top" keyword as Microsoft SQL Server has.