woensdag 18 juli 2007

Counting in SQL Server: SELECT COUNT(*) != SELECT COUNT(columnname)

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:

idname
1Name1
2NULL
3Anothername

performing this statement:

SELECT COUNT(*) FROM table
gives us 3 as a result, while doing this:
SELECT COUNT(name) FROM table
returns 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:

Rick van den Bosch zei

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

Frederik Gheysels zei

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.

Rick van den Bosch zei

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.