Is Count(*) faster than Count(Field)?

So I know not to do a Select * unless I need all the fields, but what about
Count(*)? Is that somehow (internally) slower than Count(FieldName)?First, is the column in question allow NULLs, then the two options doesn't e
ven do this same thing.
COUNT(*) counts all rows, where COUNT(colname) count rows where value for th
e column isn't NULL.
Assuming the column is defined as NOT NULL, then there is a possibly perform
ance advantage of using
COUNT(*), as SQL server can use a non-clustered index on *any* column in the
table to count the
rows. Hopefully, SQL Server will pick a non-clustered index on the smallest
column, where you fit
most rows on a data page, meaning the least amount of I/O. If you specify a
certain column, then SQL
Server *might* be smart enough to realize in simple cases that using any ind
ex is the same as using
an index over that particular column, but I wouldn't trust it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what abou
t Count(*)? Is that
> somehow (internally) slower than Count(FieldName)?
>|||I saw someone suggested using count(1) and said that is the fastest to count
rows. Just like gurus to confirm.
James
"D. Patrick" wrote:

> So I know not to do a Select * unless I need all the fields, but what abou
t
> Count(*)? Is that somehow (internally) slower than Count(FieldName)?
>
>|||You can theoretically find a *slightly* faster initial parse time for that q
uery compared COUNT(*)
(at least this holds for EXISTS, I take it the same is for COUNT()). But the
execution plan should
be the same, and we are talking about a few clock-cycles here, nothing you w
orry about in an RDBMS.
It is unlikely that you will be able to even measure such a difference with
any significance (unless
you have some 1000 columns in the table, and I still don't think you'll see
a significant
difference). And, remember this is parse time for the query, not something t
hat happens for every
row, or even every execution (assuming your plans are re-used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0850C483-D5D2-4614-8C74-6770B5162836@.microsoft.com...[vbcol=seagreen]
>I saw someone suggested using count(1) and said that is the fastest to coun
t
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
>|||It has been said about Oracle that this makes a difference. I don't know
about Oracle, but on SQL Server it makes zero difference. "SELECT
COUNT(1) FROM ..." is no faster than "SELECT COUNT(*) FROM ..." (it is
equally fast).
Gert-Jan
James Ma wrote:[vbcol=seagreen]
> I saw someone suggested using count(1) and said that is the fastest to cou
nt
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
>|||why dont you use the report on the query to see the time expend in such
query?
Bruno Alexandre
(a Portuguese in Denmark)
"D. Patrick" <replywithinthegroup@.thenotreal.com> escreveu na mensagem
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what
> about Count(*)? Is that somehow (internally) slower than
> Count(FieldName)?
>