Re: just a silly thing maybe...

From: Steve Kass (skass_at_drew.edu)
Date: 11/11/04 

Date: Thu, 11 Nov 2004 09:41:54 -0500

Roji,

  What do you mean by "better results"? I don't see anything at the
link you posted to suggest that. It's very unlikely that
count(T.columnName) will be faster than count(*). They should be
evaluated in the same way if T.columnName is constrained to be NOT NULL
and T is not on the right/left side of a left/right outer join. In
other cases, you would choose the one that gives you the answer you
want, and you wouldn't choose to calculate a different answer because
it's faster, but even if you had a choice, count(*) will probably be
faster than count(columnName), unless there is something strange about
the table data or statistics that leads the optimizer to a bad query
plan and that has nothing to do with the count() aggregate.

Steve Kass
Drew University

Roji. P. Thomas wrote:

>Enric,
>
> SELECT Count(*)
> is equivalent to
>
> SELECT COUNT('AnythingGoesHere')
>
> But SELECT COUNT(fieldName) can give you better results
> (also different results dependas on the NULLability), based on the
> indexes available.
>
> See :
>http://toponewithties.blogspot.com/2004/08/count-vs-countcolname.html
>
>

Relevant Pages

  • Re: From clause with parenthese - Posted once but server error
    ... Steve Kass ... Drew University ... Kalvin wrote: ... >documentation on this, but am working on a procedure written by ...
    (microsoft.public.sqlserver.programming)
  • Re: What GROUP BY is not...
    ... Does the jet engine sort the selected data before grouping them? ... Server using the same plan of executions, and, recently, explanations ... AGGREGATE is free lunch by comparison with the same statement WITHOUT ...
    (microsoft.public.access.queries)
  • Re: RTRIM function doesnt work
    ... replace it, as suggested, with CHAR, the "regular" space. ... Scott wrote: ... >>Steve Kass ... >>Drew University ...
    (microsoft.public.sqlserver.programming)
  • Re: What GROUP BY is not...
    ... You are also missing that GROUP BY WITH AGGREGATE can use the similar query ... Jet is inefficient in both cases, I mean, with or without ... I NEVER said that result that are identical implies the execution plan ... Group By queries. ...
    (microsoft.public.access.queries)
  • Re: GetDate as Parameter for UDF Function returns table
    ... Functions, expressions, and column references cannot ... Steve Kass ... Drew University ... Liam Ponder wrote: ...
    (microsoft.public.sqlserver.server)