Today, I've learned something new; or rather, I was being pointed out something that I didn't know about:
It seems that, doing a SELECT COUNT(columnname) does not always give you the same results as doing a SELECT COUNT(*). As it was pointed out to me, doing a SELECT COUNT(columnname) returns the number of rows in the resultset in where the value of the field in 'columnname' is NOT NULL.
This means that, given the following set of data:
| id | name |
| 1 | Name1 |
| 2 | NULL |
| 3 | Anothername |
performing this statement:
SELECT COUNT(*) FROM tablegives us 3 as a result, while doing this:
SELECT COUNT(name) FROM tablereturns 2 as result.
I really was ignorant to this behaviour, as I do (or did), most of the time a SELECT COUNT(1) FROM table instead, since I thought that this was the most performant option, but this also seems to be not true in some circumstances;
doing a SELECT COUNT(*) enables SQL Server to use indexes in calculating the number of results in a resultset.
For a more thourough article about this, I'd like to refer to this article on SQL Server Central.
Thanks to Peter De Boer for pointing this out
3 opmerkingen:
Hi Frederik. When you think of it, it's pretty 'normal' this works the way it does... When you execute 'SELECT name FROM table' you won't get 3, but 2 rows in your resultset. So why would COUNT(name) be any different?
And when you would like to use a columnname in your count, just use the primary key of the table. That column will always contain a value and uses an index ( or at least I hope so ;) ), so it will be pretty fast. Not sure if it's faster than select count(*), but I would think so.
Kind regards, Rick
Hmm, no, not really. When you do a select name from table, you would get a resultset consisting of 3 rows, of which one contains NULL.
I always thought that count gave a result based on the number of rows; and even if a column contains NULL, there's still a row, and that's why I find this so strange.
OK, point taken on the SELECT (name) part. I guess I couldn't think straight so late in the evening. ;)
Nevertheless, I still seem to 'understand' this works this way... It doesn't feel weird to me. I can image it does though.
Een reactie posten