Pagination With MSSQL Server Made Simple(r)

First a Brief Overview of SQL Server 2005 Paging Headaches.

If you have ever found yourself working in a web development environment backed by SQL Server you undoubtedly have adapted a hatred for pagination. This hatred is only further fueled by knowing how easy it is with MySQL with the aid of the LIMIT operator. Prior to SQL Server 2005 developers were left with unintuitive and tedious stored procedures that would create temp tables prior to serving the desired results.

Now let's look back at the title of this post "Pagination With MSSQL Server Made Simple(r)"... MSSQL 2005 doesn't offer an operator with the equivalent of MySQL's LIMIT, but I'll show you the next best thing.

Using Straight SQL you can make use of the ROW_Number() function as described below.

SELECT Title, Description, User
FROM (SELECT ROW_NUMBER() OVER (ORDER BY created DESC)
AS Row, Title, Description, User FROM Blog)
AS BlogLimited
WHERE Row >= 6 AND Row <= 12

This can be thrown into an easier to use Stored Procedure.

CREATE PROCEDURE usp.BrowseBlog
@PageNumber INT,
@ResultsPerPage INT
AS

BEGIN

WITH BlogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY created DESC)
AS Row, Title, Description, User
FROM Blog)

SELECT Title, Description, User
FROM BlogEntries
WHERE Row between 
(@PageNumber - 1) * @ResultsPerPage + 1 and @PageNumber*@ResultsPerPageEND 

Hopefully this will make your lives a little easier and reduce some of that stress :)