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.
[cc lang=”sql”]
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)
[/cc]

One comment
Darian 23 Aug 2018 at 12:34 pm

Hi, I have two question about this solution:
1 – Top(@rowsPerPage * @pageNum) is right ? Are you losing some rows?
2 – In sentence select *
FROM SQLPaging
where ResultNum > ((@pageNum – 1) * @rowsPerPage). Can you use TOP (@rowsPerPage) for more perfermance?

Thanks!

Featured Articles

 Site Author

  • Thanks for visiting!
css.php