Advanced SQL: Auto-numbering rows for fast paging (SQL Server 2005)

adrian@arctus.co.uk (Adrian O'Connor) — Thu, 19 Apr 2007 08:02:00 -0400

I have always been an advocate of performing as much data-shaping as possible inside my stored procedures. Stored procedures are far better (and significantly faster) at working with data than .NET can ever be.

For serious applications, this meant handling ‘paging’ inside the procedure. Typically, my procedures would take a page number as a paramter and, using an in-memory TABLE variable with an IDENTITY column, return just the results for the current page.

With SQL Server 2005 there is now a better way. The system function rank() can work out a row number for our results on the fly. As always, examples are for NorthWind:

SELECT *, rank() OVER (ORDER BY ProductName)
    FROM Products;

If you run the query, you will see that the last column is an auto-incrementing field.

That looks great - absolutely perfect for paging, and some of you even probably tried doing something like this already:

SELECT *, rank() OVER (ORDER BY ProductName) AS rank
    FROM Products
    WHERE rank BETWEEN 11 AND 20

Yikes. It looks good, but sadly the result of rank() isn’t available to the WHERE clause (because rank runs after the WHERE to number only the results that actually match our criteria).

We can get around this by ranking our results in a sub-query. An outer query can restrict the rows based on the value produced by rank. This means that our sub-query must contain the main where clause for any search criteria we might have (because only the successful matches will be ranked), and the OVER (ORDER BY … must be the order we wish our results to be returned in. Here’s the full query (written to use WITH instead of a regular sub-query):

WITH ProductRowNumber (ProductId, RowNum) AS 
(
    SELECT ProductId, rank() OVER (ORDER BY ProductName) AS RowNum
        FROM Products
)
SELECT *
    FROM Products p
    INNER JOIN ProductRowNumber pn
        ON pn.ProductId = p.ProductId
    WHERE pn.RowNum BETWEEN 11 AND 20
    ORDER BY pn.RowNum

You would need to work out the values passed to BETWEEN based on the current page number (and size), but other than that it’s ready to go.

Arctus Blog: Advanced SQL: Auto-numbering rows for fast paging (SQL Server 2005)

Advanced SQL: Auto-numbering rows for fast paging (SQL Server 2005)