Is there any difference between COUNT('') and COUNT(*) and COUNT(1) and COUNT(ColumnName)? What approach is faster?
Asked
Active
Viewed 866 times
2
Rami Alshareef
- 7,015
- 12
- 47
- 75
Alexandre
- 13,030
- 35
- 114
- 173
-
1possible duplicate of [Count(*) vs Count(1)](http://stackoverflow.com/questions/1221559/count-vs-count1) – onedaywhen Sep 21 '11 at 07:23
2 Answers
6
Count(ColumnName) is influenced by the value of the column. The other variants do effectively the same.
Count(*) is slower in some databases (MySQL amongst others), because it retrieves all fields while it doesn't have to. That's whay often 'x' or 1 is used to be safe. SQL Server and Oracle are somewhat smarter and don't retrieve field values if they don't have to.
Note that '' equals NULL on Oracle (yes it does!), which may have an undesired effect there. Not a problem for SQL Server, but you can use 1 to be safe.
GolezTrol
- 114,394
- 18
- 182
- 210
-
"which *may* have an undesired effect.". Nice one. That **is** certainly an undesired effect, because it will always return 0 :-) – Daniel Hilgarth Sep 21 '11 at 07:27
4
COUNT(''), COUNT(1) and COUNT(*) will return the same result. COUNT(ColumnName) might return a different value, because COUNT only counts non-null values.
Performance-wise they should be equivalent, at least on SQL-Server.
Daniel Hilgarth
- 171,043
- 40
- 335
- 443