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:


  1. CREATE TABLE MonthlyProfit  
  2. (  
  3.     yearid int,  
  4.     Monthid int,  
  5.     Profit bigint  
  6. )  
  8. INSERT INTO MonthlyProfit (yearid, Monthid, Profit)  
  9. VALUES  
  10. (2010,1,1766), (2010,2,100), (2010,3,1500), (2010,4,15000), (2010,5,900), (2010,6,45),  
  11. (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:


  1. DECLARE @pagesize INT = 6  
  2. DECLARE @pagenumber INT = 2  
  4. ;WITH cte AS  
  5. (  
  6. SELECT   
  7.     yearid,   
  8.     monthid,   
  9.     profit,  
  10.     ROW_NUMBER() OVER(ORDER BY yearid, monthid) AS rn  
  11. FROM MonthlyProfit  
  12. )  
  13. SELECT  
  14.  yearid, monthid, profit  
  15. FROM cte  
  16. WHERE rn between ((@pagenumber - 1) * @pagesize + 1) AND (((@pagenumber -1) * @pagesize) + @pagesize)  
  17. ORDER BY rn  
  18. And this is how simple it can be done in Denali:  
  20. DECLARE @pagesize INT = 6  
  21. DECLARE @pagenumber INT = 2  
  23. SELECT   
  24.  yearid,   
  25.  monthid,   
  26.  profit  
  27. FROM MonthlyProfit  
  28. ORDER BY yearid, monthid  
  29. OFFSET (@pagesize * (@pagenumber - 1)) ROWS  
  30. 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 :)


Geniiius ApS