My Strange Cat @ EmilyStrange.com ROUND 2 - MS SQL 2000 vs. Pagination

In all my excitement while creating My Strange Cat, I overlooked a bug in MS SQL 2000. Well, not really a bug... more like a missing feature. Most current SQL databases feature the ability to paginate your query results using LIMIT and OFFSET (or something similar), where LIMIT is the number of rows you want to return and OFFSET is the row that you want to start at. This keeps pagination nice and simple, and keeps your queries to the minimum size necessary. Check out Pete Freitag's blog about it.

Unfortunately, MSSQL 2000 does not support this feature, or ANY kind of simple row counting functionality. The closest you can get to actual pagination is by using the TOP keyword with 2 subqueries--see this example by Joseph Lindsay. This doesn't quite work the same though--when viewing the last page of results the SQL server is working just as hard as (or harder than) it would without this form of paging.

The only other solution I could find was to query the server for ALL the records in the table, cache that query with cfquery's "cachedWithin" attribute, run subqueries using Coldfusion's query of queries feature (which is mighty cool, I might add.) and then set the "startrow" and "maxrows" attributes in my cfoutput tag to paginate the results when it is time to display the data.

This solution works well because it takes the load off of SQL and puts it onto Coldfusion. I only have to run one big SQL query every so often (currently every half hour) and then Coldfusion does the rest of the work. This is a huge upgrade to my previous code which ran that same big SQL query for every single page hit (ouch!) Keep in mind this solution should only be used with small recordsets. If I had a table with 50,000 rows and a few ntext columns, I doubt my server would still be running because it would try to cache the whole table (and fail miserably.)

And in case you are wondering what I'm going to do when my table does get to 50,000 rows or some other obscene number, I have a couple backup plans. First--I wrote another solution for my app using the TOP / ORDER BY method and stored it away in a safe place. Second, if I absolutely have to I will make my future self do the work of recreating the table in MS SQL 2005 and copying all the data over, then rewriting the code to use LIMIT and OFFSET.

All I can say is, "Why didn't I just create database in MS SQL 2005 in first place?!?" BAH!