SQL Server Performance

count(*) function

Discussion in 'Performance-Related Article Discussions' started by grg_sqlserverperformance, Jan 8, 2007.

  1. Hi
    is there any difference in performance e in using count(*) rather than count(SomeColumn)?

    I feel that Count(PrimaryKeyColumn) is faster and efficient way than Count(*). Am I correct?
    Thanks in advance
    Guru
  2. Adriaan New Member

    COUNT(SomeColumn) will not count rows where SomeColumn is null, so beware of the difference.

    Just use COUNT(*) if you want to know the number of rows. Performance will be affected mainly by the degree to which there are covering indexes for your filter criteria (if you have any).
  3. Roji. P. Thomas New Member

  4. FrankKalis Moderator

    Just to add to Adriaan: Apart from that NULL-awareness of COUNT(), if you use COUNT(*) you leave the decision to the engine how to get the result of that query. The engine is very likely to choose the narrowest index available. If you use COUNT(column) you force the engine to count on that column, be that a wide or narrow one. And if there is no index available on that column, you force a scan.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. Roji. P. Thomas New Member

    quote:Originally posted by FrankKalis
    If you use COUNT(column) you force the engine to count on that column, be that a wide or narrow one.

    If the column is not NULLABLE, it will still use the smallest index available. (eg is in the link I posted above)


    quote:
    And if there is no index available on that column, you force a scan.
    AFAIK, COUNT can be achieved only with a scan. When there is no index on the column specified, the result wil be clustered index scan.


    Roji. P. Thomas
    http://toponewithties.blogspot.com
  6. FrankKalis Moderator

    Thanks for the specification. Blame it on this early morning here in my place that I was a bit loose in my wording. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page