Counting, counting, counting …

8 Jun 2007 1 mins Databases in general

OK, not so describing title, but …

A few days ago, when I was talking with my colleague, and discovered interesting fact that some people doesn’t know about count function behavior in several cases. Let me show small example.

Create database on your favourite server. Then create simple table, something like create table test (id int);. Insert some data (null values too). Now try to execute these two statements: select count(*) from test; and select count(id) from test;. Wow! You got different results.

In fact it’s not so wow how it looks like. Yep, count(*) counts null values too but count(some_column) counts only not null rows (not null in this column). And it’s obvious. The count(*) has no idea whether to count rows, where first column is null and all others not or last is null and others not or … who knows. But when you specify column name, then this is straightforward.

I hope this isn’t new for you. 😉

Jiří Činčura is .NET, C# and Firebird expert. He focuses on data and business layers, language constructs, parallelism, databases and performance. For almost two decades he contributes to open-source, i.e. FirebirdClient. He works as a senior software engineer for Microsoft. Frequent speaker and blogger at www.tabsoverspaces.com.