Why the count(*) in a sql prepare statement type is really small?

I found the "count() over()" will be much faster compare to the "select count() from table".

e.g

use count(*) over

with CTE as( select col_A,col_B,totalNumber=count(*) over() from table1 where conditions..) select totalNumber from CTE

use select count(*) from ( or use count(1) as well)

select count(*) from table1 where conditions..

In my local testing on SQL Server 2K5, the count() over* will be 4X faster if the search criteria is complex and the rows returned is large.

But why the count(**) over perform so faster?

Thanks in adv.

Vance

Update

I think really missed some details:

Actually I use "prepare statement" sql for my test like:

exec sp_executesql N'SELECT count(*) FROM tableA WHERE (aaa in(@P0)) AND (bbb like @P1)', N'@P0 nvarchar(4000),@P1 nvarchar(4000)',N'XXXXXXX-XXXX-XXX',N'%AAA%'

Execution Plan says "HashMatch" cost 61%, others is "index seek". And the execution time will be 1484ms and logical reads around 4000.

This is slow when compares to

SELECT count(*) FROM tableA WHERE (aaa in('XXXXXXX-XXXX-XXX')) AND (bbb like '%AAA%') Execution plan says "clustered index seek" cost 98%. And the execution time is 46ms and logical reads will be 8000.

And if changes to the first sql to :

exec sp_executesql N'with CTE as( SELECT total=count(*) over () FROM tableA WHERE (aaa in(@P0)) AND (bbb like @P1)) select top 1total from cte', N'@P0 nvarchar(4000),@P1 nvarchar(4000)',N'XXXXXXX-XXXX-XXX',N'%开发者_开发百科AAA%' Execution plan says "clustered index seek 58%', no "hashmatch join" occurs.

And the execution time is 15ms and logical reads is: 8404.

so, does "hash match join" has much overhead for the performance?