Paging record sets in SQL Server 2005

Recently I have been working on a content management system that I am developing, and I came across a performance challenge with large content sets. A typical query against a very large dataset might return tens of thousands, hundreds of thousands, or even millions of records from the database, but typically only a single page of those records will be displayed at any given time. The rest of the returned data represents wasted cycles and bandwidth on both the database and application servers.

I spent some time looking around for solutions to this problem, and I found several good solutions to return only one page of records from the database, but these solutions had limitations, either in that they required the use of stored procedures, or that they required making a second database call to get the total number of records matching the query. In particular, Ben Nadel posted a solution that generated some good discussion and links to other posts.

My solution, for SQL Server 2005, takes a slightly different approach than others I have seen. I use the WITH, ROW_NUMBER(), and OVER features of SQL Server 2005 to generate the paged recordset, and I use a UNION query to tack on an extra row with the total records matching the query. I haven't tried this out on other platforms, but the same strategy should work for any database platform that supports some form of paged recordset retrieval- MySQL, Oracle, and Postgres should all work.  

Here is a query of all the records in the object repository, but returning only 20 at a time. 

WITH cmObjects
AS
(
    SELECT         ROW_NUMBER() OVER (ORDER BY objectname) AS Row, o.*, t.TypeName, null as total
    FROM        cmObject o
    JOIN        cmType t on o.TypeID = t.TypeID
)
SELECT    *
FROM    cmObjects 
WHERE    Row BETWEEN 1 AND 20           

UNION

SELECT         null,null,null,null,null,null,null,null,null,null,null,null,null, count(*) as total
FROM        cmObject o
JOIN        cmType t on o.TypeID = t.TypeID