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

I do this by putting the whole resultset with the row_number into a temp table, then use the @@rowcount from that and use the query on that to return the page of data I need.

Answer 2

If count(*) is slow you really need to address that issue first by carefully examining your indexes and making sure your statistics are up to date.

In my experience, there is nothing better than doing two separate queries, one to get the data page, and one to get the total count. Using a temporary table in order to get total counts is a losing strategy as your number of rows increases. E.g., the cost of inserting 10,000,000 million rows into a temp table simply to count them is obviously going to be excessive.

Answer 3

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 have to do two queries.