Efficiently Returning Paged Data from T-SQL

Using a Common Table Expression (CTE) provides a very efficient way to return paged data along with a total row count from Microsoft SQL 2008.

Here, we assume @PageNo and @PageSize have been set by the caller. @PageNo is the page number for which we wish to return rows. @PageSize is the desired number of rows per page.

-- Calculate the ROW_NUMBER() range we wish to return...
DECLARE @StartRow INT = ((@PageNo - 1) * @PageSize) + 1
DECLARE @EndRow INT = @StartRow + @PageSize - 1

;WITH cols
AS
(
SELECT number,
ROW_NUMBER() OVER(ORDER BY number) AS seq
FROM tallynumbers
)
SELECT number, (select count(*) from cols) as TotRows
FROM cols
WHERE seq BETWEEN @StartRow AND @EndRow + 49
ORDER BY seq

NOTES:
The T-SQL command ROW_NUMBER() is “one-based”.