Server side paging using Common Table Expressions

Posted 09 May 2007 20:10 by Kristof Michiels

If you're not into under-the-hood technobabble: do yourself a big favor and skip this post. Yesterday I've rewritten the query for fetching blog posts from the back-end database. Not only I wanted it to be cleaner written and faster, but my pal and I were also in desperate need of 'Previous entries' - 'Next entries' buttons at the bottom of the page (this is called paging). The addition of this tiny feature would allow us to reduce the number of posts shown on the homepage to e.g. five.

Since I'm using SQL Server 2005 as the back-end database, I was able to solve the problem using Common Table Expressions (CTEs). A CTE is a temporary named result set, derived from a simple query, in this case a SELECT query. CTEs can be thought of as an alternatives to derived tables (subquery), views, and inline user-defined functions. In addition, SQL Server 2005 adds the ROW_NUMBER function, which is useful in combination with CTEs. Before SQL Server 2005, CTEs weren't possible and server side paging needed to be done using dynamic SQL or nested TOP clauses.

I'm not going to list the entire query here (can be provided on simple request), but instead refer you to a resource that has been extremely helpful to me: this article on Server Side Paging. Yes, it's time to get it out in the open, I use Microsoft technologies (but I do like Ruby and Rails too). I've tried hard to make the result look technology-agnostic (e.g. the clean URL structure instead of the .aspx suffix) and standards compliant, but the blog-engine has been entirely handcrafted in Dotnet. So no Wordpress, Blogger, Movable Type or Drupal for me. How great and empowering these solutions may be (and I really think they are), I guess I'm just one of those people who likes DIY'ing and tweaking too much to leave the fun work to others.