<< Installing SQL Server 2000 on Windows Vista | Home | How to calculate working time? >>

Row_number() makes life easier

Today I started using row_number() for paging in replies in a forum on a web site (see for example http://www.orthopedie.nl/lotgenoten/default.asp).

Before SQL Server 2005

Before SQL Server 2005 I also used paging, but I had to do that with a temporary table and with dynamic SQL. That works as well, but the penalty is that you need a number of writes and reads in tempdb.

The code looked like this:

CREATE TABLE #t (trID int)
INSERT INTO #t (trID)
SELECT rID FROM tblReply
WHERE rMessageID = @intMessageID
/* Count the number of replies */
SELECT @intTotalRecs = COUNT(trID) FROM #t
/* Determine the number of pages */
SELECT @intTotalNumberOfPages =
  CEILING(CAST(@intTotalRecs AS decimal(6,2)) / CAST(@intPagesize AS decimal(6,2)))


And then I used dynamic SQL to first select all replies up to the requested page (with a TOP clause) and then take the first replies from that recordset (with another TOP clause).

Anyhow, this takes more effort than is necessary.

New code in SQL Server 2005 and higher

The new code basically looks like this:

 
SELECT ...
FROM tblReply 
WHERE rID IN
    (SELECT rID --, MessageNumber
     FROM (SELECT row_number() OVER (ORDER BY rDatum) AS MessageNumber, rID
           FROM tblReply
           WHERE rMessageID = @intMessageID) Messages
     WHERE MessageNumber BETWEEN @intBegin AND @intEnd
    )
ORDER BY ReplyDate

I left out the code to calculate the begin and end numbers of your page. That depends on your situation.

posted @ Wednesday, June 30, 2010 4:56 PM

Print