January 11, 2010 at 9:48 am
Is there any advantage/disadvantage as far as rendering, performance, etc. by using either select count(*) or select count(0) to get total records on a table?
I've always used count(*) but somebody just asked me that question and had me stumped, so I thought I ask some of the experts.
Let's start the weigh in...
thx,
John
January 11, 2010 at 9:51 am
there is no difference; SQL is smart enough to know there is no reason to retrieve rows for the count() function, and does it the fastest way possible; count(*),count(someColumn) and count(1) are all the same as far as SQL server is concerned.
if you look at the execution plan of each query, you'll see that they are identical if the only thing you changed was what is inside the parenthesis of the count() statement
Lowell
January 11, 2010 at 11:00 am
Lowell (1/11/2010)
there is no difference; SQL is smart enough to know there is no reason to retrieve rows for the count() function, and does it the fastest way possible; count(*),count(someColumn) and count(1) are all the same as far as SQL server is concerned.if you look at the execution plan of each query, you'll see that they are identical if the only thing you changed was what is inside the parenthesis of the count() statement
Count(*) can be faster as count(someColumn) may need to check if there are nulls in the column if the column is nullable
January 11, 2010 at 1:00 pm
Lowell (1/11/2010)
count(*),count(someColumn) and count(1) are all the same as far as SQL server is concerned.
Not true at all.
Count(*) and count(<constant>) both count the number of rows in the resultset.
Count(someColumn) counts the number of rows in the resultset where someColumn is not null
They are functionally different and perform differently as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy