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 even do this same thing.
COUNT(*) counts all rows, where COUNT(colname) count rows where value for the column isn't NULL.
Assuming the column is defined as NOT NULL, then there is a possibly performance 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 index 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 about 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 about
> Count(*)? Is that somehow (internally) slower than Count(FieldName)?
>
>|||You can theoretically find a *slightly* faster initial parse time for that query 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 worry 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 that 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...
>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 about
>> Count(*)? Is that somehow (internally) slower than Count(FieldName)?
>>|||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:
> 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 about
> > Count(*)? Is that somehow (internally) slower than Count(FieldName)?
> >
> >
> >|||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)?
>