A lot of people seem to have been asking about this recently. They have a T-SQL query which uses a call to row_number() within a CTE or derived table, and uses this row_number() field to filter the results to a single page – but they also want to know how many rows were in the whole set (rather than just in the page). @@rowcount doesn’t do it for them, because this will return the number of rows in that page.
Something like this:
select *
from
(select *, row_number() over (order by employeeid) as rn from dbo.Employees) e
where rn between 11 and 20
One good way of doing this is to use an aggregate with the OVER() clause. Like this:
select *
from
(select *, row_number() over (order by employeeid) as rn, count(*) over() as cnt from dbo.Employees) e
where rn between 11 and 20
This is a way of having an aggregate as part of your resultset, without having to restrict your results with a GROUP BY statement. Now your code can access the total number of rows without having to re-query the data. And furthermore, this method can work out the aggregation using the single pass of the data that was already being used.
Of course, there is the downside of returning an field which repeats all the way through – but this may well be ‘less bad’ than re-querying the data to get the record count.
