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)
