Post reply

Count(1) Vs Count(*)

  • March 25, 2008 at 6:38 am

    #121081

    Hi,

    There is always this notion that count(1) is better than count(*) but i have read in an article that both performs the same way. Can anybody clear on this.

    Thanx in advance,

    Balamurugan G

  • March 25, 2008 at 6:58 am

    #793449

    I've just tested both on a table containing 4.7 million rows and I get the exact same io stats for both:

    Scan count 5, logical reads 8283, physical reads 4, read-ahead reads 8180, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    The cpu time and the elapsed times vary between different executions but they're roughly the same (around 850ms) give or take 50ms.

    Seems like there's no difference there but I'd be curious if this isn't always the case.

    Karl
    source control for SQL Server

  • March 26, 2008 at 6:04 am

    #793960

    Hi,

    Can you please tell me how did you got IO stats for just one query.

    I'm new to SQL and am trying to learn some techniques for Performance Tuning. I believe this could be of great help in future.

    Thanks

    Ankit

  • March 26, 2008 at 6:36 am

    #793975

    Ankit Mathur (3/26/2008)

    Hi,

    Can you please tell me how did you got IO stats for just one query.

    I'm new to SQL and am trying to learn some techniques for Performance Tuning. I believe this could be of great help in future.

    Thanks

    Ankit

    Ankit,

    You can enable IO/Time statistics like so:

    set statistics io on

    set statistics time on

    Note that the statistics will remain on for the connection until you set the to off, or you close the connection.

    Karl
    source control for SQL Server

  • March 26, 2008 at 9:11 am

    #794119

    Shouldn't be any difference.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply