Results 1 to 9 of 9
  1. #1
    Join Date
    May 2002
    Posts
    39

    Unanswered: count(1) versus count(*)

    Hi All

    Have a very simple and basic question...

    Can somebody point out in detail what the differences are between
    count(1) and count(*) ??

    say
    select count(1) from <table>
    or
    select count(*) from <table>

    Which is better to use, why, limitations etc ..

    Thanks a lot!

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Doesn't matter. You should probably stick to COUNT( * ) for clarity -- because that is what you really mean.
    Thanks,

    Matt

  3. #3
    Join Date
    May 2002
    Posts
    39
    Hello Matt

    Don't think this is entirely correct.
    I believe count(1) is inherently quicker...since the Sybase projection doesn't involve any columns of the table, and "1" serves as a constant value projection.
    The count(*) leads to a projection of all the columns of the table and hence leads to greater overhead.

    Have you heard of any explanation similar to the above??
    Are there any other differences??

    Thanks a lot.

    Also...any idea on my other "isql help" post??

    Thanks again.

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    I have read in DB2 that count(1) performs better than count(*). Still looking for same comparison in Sybase.

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Look at the showplan and statistics I/O. You'll see that they're the same. The optimizer isn't that dumb.
    Thanks,

    Matt

  6. #6
    Join Date
    May 2002
    Posts
    39
    Oh...thats wonderful!
    Didn't think of that

  7. #7
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Also if you do not need the exact number of rows you can pull it from a system table (the one that shows when you do sp_help foo). I can't quite remember which one it is.
    Thanks,

    Matt

  8. #8
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Here's the ASE manual for how ASE optimizes the count( * ):
    http://sybooks.sybase.com/onlinebook...6705;pt=6705?D
    Thanks,

    Matt

  9. #9
    Join Date
    May 2002
    Posts
    39
    Are the query plans different if there is a where clause??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •