Ever since I was a web developer back in the days, server side paging has been an issues. Back then we returned all the rows from the database and did the paging on the client (webserver). Looking back, that is probably not the best and most efficient way to do it – best case is just to return the x rows needed from the database, this would speed up the application, and remove some preasure from the database.

SQL Server Denali comes with paging support, enhancements has been made to the ORDER BY cluase. The new feature comes with to new keywords OFFSET and FETCH NEXT/FIRST. OFFSET sets the number of rows to be skipped before returning rows and – the value can be a constant value, result of a query or an expression. The FETCH keyword can be used with either NEXT or FIRST – they do exactly the same, it is followed by the number of rows to be retrieved.

Well, no more writing, let’s look at some code. First of all I set up some simple demo data:

CREATE TABLE MonthlyProfit
(
    yearid int,
    Monthid int,
    Profit bigint
)
INSERT INTO MonthlyProfit (yearid, Monthid, Profit)
VALUES
(2010,1,1766), (2010,2,100), (2010,3,1500), (2010,4,15000), (2010,5,900), (2010,6,45),
(2010,7,1766), (2010,8,9100), (2010,9,-100), (2010,10,50), (2010,11,900000), (2010,12,6575)

Now i would like to have to paramters in my code, @pagenumber and @pagesize. @pagesize beeing the number of rows pr page and @pagenumber beeing the actual page to show rows from. Before SQL Server Denali the code to implement that could look something like this:

DECLARE @pagesize INT = 6
DECLARE @pagenumber INT = 2
;WITH cte AS
(
SELECT
    yearid,
    monthid,
    profit,
    ROW_NUMBER() OVER(ORDER BY yearid, monthid) AS rn
FROM MonthlyProfit
)
SELECT
 yearid, monthid, profit
FROM cte
WHERE rn between ((@pagenumber - 1) * @pagesize + 1) AND (((@pagenumber -1) * @pagesize) + @pagesize)
ORDER BY rn

And this is how simple it can be done in Denali:

DECLARE @pagesize INT = 6
DECLARE @pagenumber INT = 2
SELECT
 yearid,
 monthid,
 profit
FROM MonthlyProfit
ORDER BY yearid, monthid
OFFSET (@pagesize * (@pagenumber - 1)) ROWS
FETCH NEXT (@pagesize) ROWS ONLY

Personally I can’t wait till this product is shipped, this feature and many of the others that we have written about on this blog is going to make life much easier for developers all over the world. Happy paging