sql - Efficient way of getting @@rowcount from a query using row_number

Question

I have an expensive query using the row_number over() functionality in SQL Server 2005. I return only a sub list of those records as the query is paginated. However, I would like to also return the total number of records, not just the paginated subset. Running the query effectively twice to get the count is out of the question.

Selecting count(*) is also out of the question as the performance is absolutely terrible when I've tried this.

What I'd really love is @@ROW_NUMBERROWCOUNT :-)

Answer 1

Check out the COUNT(*) aggregate when used with OVER(PARTITON BY..), like so:

    SELECT
     ROW_NUMBER() OVER(ORDER BY object_id, column_id) as RowNum
    , COUNT(*) OVER(PARTITION BY 1) as TotalRows
    , * 
    FROM master.sys.columns

This is IMHO the best way to do it without having to do two queries.