January 8, 2008 at 11:53 am
ive saw count(1) used instead of Count(*) in a few articles, however i dont understand how it works. can anyone explain?
January 8, 2008 at 11:56 am
I don't really know, but I think there may be a performance reason to do this with some versions of Oracle.
I don't think there is any good reason to do it with SQL Server.
January 8, 2008 at 12:09 pm
Really you are just telling SQL server to count a static value. SQL Server will think the 1 is a hard coded value put at the end of each row and will count this for each row, which returns a count of the entire table.
It is like using a select like this
select 'Adam'
from mytable
Adam is not a column of my table but is part of the select statement. You can actually supply any value you want in the the count statement.
e.g. select count(1685615661) returns one result because it counts the number of times 1685615661 appears in the select statement which is once per row.
January 8, 2008 at 12:10 pm
I would also like to add that there may or may not be performance issues with doing this method. I personally will stick to best practices.
January 8, 2008 at 12:11 pm
It has something to do with actually reading the data page from disk vs. returning a static value of 1. If all of the data is not within the index used to resolve the query, the DB has to read the page where the table row exists. Like Michael Valentine Jones said, this may be an Oracle thing, I'm not sure. I always use 1 on both platforms.
January 8, 2008 at 12:50 pm
An example of where its used is here:
http://www.4guysfromrolla.com/webtech/071906-1.shtml
its used about half way down in a grey section of code. why does the article author use that here?
January 8, 2008 at 1:16 pm
I have done a little research and it seems that if any constant is put into count the SQL server database engine will automatically interpret this as count(*). The reason count(1) was used is for propietary database engines such as PL/SQL, where using count(1) helps performance.
In a nutshell, I believe the answer to the original post is old habits die hard and people have the belief that there is a performance gain to using count(1), while there is little to no evidence to support any performance gain.
January 8, 2008 at 1:38 pm
Since none of the previous posters mentioned this, let me add a couple of points. If you use the following data set:
create table test (test int)
insert test select null
insert test select 1
select*
fromtest
And then run these selects:
selectcount (1) [count_static], count (*) [count_static_or_all_rows], count (test) [count_column]
fromtest
You'll see the difference between counting a static value and counting a particular column value (test). When you count a column, null values are not counted, while when you count a static value, every row is counted. Essentially, count (1) and count (*) are equivalent syntaxes, as both return the number of table rows without any regard for whether the columns in the row contain values. Incidentally, there are many circumstances where only counting the rows where a column contains a value (non null) is exactly what is needed.
January 8, 2008 at 1:42 pm
Very informative example Eric. Thanks for your efforts.
Can I just say I hate NULL values!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply