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
The T-SQL command ROW_NUMBER() is “one-based”.