Number of rows considered by a query

Is there a way to find out how many rows are considered by a query without performing a cou开发者_Go百科nt(*) on the source?

example:

 Create table test (col1 int , col2 int)

  Insert into test select 1,2 union select 2,3

  Select avg(col1) from test 

If I use @@rowsaffected it returns the number of rows returned by the query which is 1. But I would like to find out the number of rows considered by the query.

(In my original case the source is a view that contains many tables.)

SELECT AVG(col1),
       COUNT(*),/*Number of rows*/
       COUNT(col1), /*Number of non null rows that were used in the Avg*/
       SUM(1) /*Avoids use of COUNT!*/
FROM   test  

Regarding the comment about COUNT taking a long time it should add precisely no additional work to your AVG query. If you try

SET SHOWPLAN_TEXT ON
GO

SELECT AVG(high)
FROM master..spt_values

GO

SET SHOWPLAN_TEXT OFF

It returns

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005]/CONVERT_IMPLICIT(int,[Expr1004],0) END))
       |--Stream Aggregate(DEFINE:([Expr1004]=COUNT_BIG([master].[dbo].[spt_values].[high]), [Expr1005]=SUM([master].[dbo].[spt_values].[high])))
            |--Clustered Index Scan(OBJECT:([master].[dbo].[spt_values].[spt_valuesclust]))

You will see it already uses COUNT (or COUNT_BIG actually) in order to return NULL if there were zero matching rows.

OK, let's do some simulations and see if it actually should take 16 minutes for count(*).

Testing the query:

 select count(*) cnt, avg (price) avg from MY_TABLE

where MY_TABLE has over 1,500,000 records, the above query takes 1m24sec and only 1sec for consequent runs, so you either have many more records or there is something else going on.

FURTHER TESTS:

running select avg (price) avg from MY_TABLE takes: 1m25sec

running select count(*) cnt from MY_TABLE takes: 4sec

SQL Server was restarted between tests.

Tested on a DELL Vostro 1500 with 2GB RAM and 2.2 GHz CORE 2 Duo and Windows 7 32 but, SQL Server 2000.