• To my knowledge as long as you try to do something like this:

    USE AdventureWorks2012

    GO

    DECLARE @totalrows int

    SELECT *, @totalrows = TotalRows

    FROM (

    SELECT JobTitle,

    BusinessEntityID,

    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...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service