To my knowledge as long as you try to do something like this:
DECLARE @totalrows int
SELECT *, @totalrows = TotalRows
ROW_NUMBER() OVER (ORDER BY BusinessEntityID) as RowNum,
COUNT(*) OVER () as TotalRows ---- nice to know how many total rows when paging to know how many pages are there
FROM HumanResources.Employee) AS t
WHERE RowNum BETWEEN 5 AND 15
you are going to get an error like this:
Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
I can't think of a way off the top of my head to get the total EXPECTED rows while also FILTERING to a restricted set for output in a single pass. You store the data and use @@ROWCOUNT and then SELECT final set out (possibly using just key values to make a smaller temp objects - I have used that to GREAT effect in paging scenarios at clients over the years) or you do one hit as a count to set the output parameter and a second to get the data. With good indexing it can still be "fast", but clearly not as fast as if you could get the total with the restricted set...
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service