SQL Server Pagination using CTE and RowNumber

This is a succinct example of a pagination implementation. The ROW_NUMBER() function is very fast and very useful. The CTE’s (Common Table Expressions) are novel, however where the rubber meets the road temp tables are usually far more efficient.

SET @rowsPerPage = 10  
SET @pageNum = 3  
 
WITH SQLPaging  
AS
(  
    SELECT Top(@rowsPerPage * @pageNum)
    ResultNum = ROW_NUMBER() OVER (ORDER BY id)
    ,id  
    FROM dbo.Table1
)  
SELECT *
FROM SQLPaging
WHERE ResultNum > ((@pageNum - 1) * @rowsPerPage)

Featured Articles

 Site Author

  • Thanks for visiting!