My favorite way to do paging with T-SQL

This is how I like to do paging with T-SQL. I've seen quite alot of ways to do paging with SQL Server, both with and without temporary tables and this is definetly my favorite as I find it both straight forward and easy to read. And yes, this i mainly a note to self :-)

DECLARE @intStartRow int;
DECLARE @intEndRow int;

SET @intStartRow = (@intPage -1) * @intPageSize + 1;
SET @intEndRow = @intPage * @intPageSize;    

WITH blogs AS
    (SELECT strBlogName, 
     ROW_NUMBER() OVER(ORDER BY intID DESC) as intRow, 
     COUNT(intID) OVER() AS intTotalHits 
     FROM tblBlog)
SELECT strBlogName, intTotalHits FROM blogs
WHERE intRow BETWEEN @intStartRow AND @intEndRow

The example above assumes that you have a table namned tblBlog that contains a column named strBlogName. It also assumes that the variables @intPage and @intPageSize are already declared and set, usually as parameters to a stored procedure.

The first four rows in the example declares two new variables, @intStartRow and @intEndRow and calculates their values based on @intPage and @intPageSize.

The seven last rows are the interesting ones. In them we first specify a subquery which in which we specify the columns we want to select (strBlogName in the example), that we want to calculate the row number based on a specific sort order (intID DESC in the example) and also that we want to calculate the total number of rows that the subquery will select. It is also here that we do any filtering, such as WHERE strBlogName LIKE '%a%'.

Finally we select the columns that we want to select plus the column with the total number of rows that the subquery returns and filter out only the rows that belongs to the specified page.

If anyone know of a better way I'd love to know :-)

PS. For updates about new posts, sites I find useful and the occasional rant you can follow me on Twitter. You are also most welcome to subscribe to the RSS-feed.