difference between COUNT(*) & COUNT(1)

Hi,
1 - select count(*) from tablename
2 - select count(1) from tablename
query 1 and 2 return the same value as output.
what is the difference between count(*) and count(1) ?
which is efficient?
Please advise
Thanks,
Soura
do
set statistics io on , client statistics ,server statistics and
execution plan in query analyzer.
you will no major different for your both queries, What you need to have
proper cluster index on the table to improve count performancae.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||SouRa wrote:
> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
No difference. If you check the execution plans you should find that
they are identical.
David Portas
SQL Server MVP
|||> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
Actually, a non-clustered index will be better. A non-clustered index on any column will cover that
query, so SQL Server can scan the leaf-level in that index instead of scanning the leaf-level in the
clustered index (which are the data pages). The more narrow the non-clustered index, the better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amish Shah" <shahamishm@.gmail.com> wrote in message news:%23ulIsi2EGHA.1508@.TK2MSFTNGP15.phx.gbl...
> do
> set statistics io on , client statistics ,server statistics and
> execution plan in query analyzer.
> you will no major different for your both queries, What you need to have
> proper cluster index on the table to improve count performancae.
>
> Regards
> Amish
> *** Sent via Developersdex http://www.codecomments.com ***
|||It's just a matter of what you prefer - which can result in discussions of
religious fervour.
Just stick to count(*) being correct and count(1) being stupid.
I think of count(*) as counting the number of rows but I have heard people
liking count(1) as meaning count where the existence of the row is true (1
meaning true).
I believe there are (or were) lesser databases where count(1) gives better
performance.
In sql server it doesn't matter for performance - just avoid count(fld)
(unless that's what you want.
"SouRa" wrote:

> Hi,
> 1 - select count(*) from tablename
> 2 - select count(1) from tablename
> query 1 and 2 return the same value as output.
> what is the difference between count(*) and count(1) ?
> which is efficient?
> Please advise
> Thanks,
> Soura
>