17

I have two choices when writing an SQL statement with the COUNT function.

  1. SELECT COUNT(*) FROM <table_name>
  2. SELECT COUNT(some_column_name) FROM <table_name>

In terms of performance, what is the best SQL statement? Can I obtain some performance gain by using option 1?

RustyTheBoyRobot
  • 5,891
  • 4
  • 36
  • 55
Upul Bandara
  • 5,973
  • 4
  • 37
  • 60

3 Answers3

25

Performance should not matter because they do 2 different aggregates

  • COUNT(*) is all rows, including NULLs
  • COUNT(some_column_name), excludes NULL in "some_column_name"

See the "Count(*) vs Count(1)" question for more

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 4
    they aren't different in case of primary keys or not null columns. – Borys Jul 12 '13 at 13:04
  • @Borys: You'll get the same count. But the primary key will have an index = better. The "not null column" column may not. Of course, it may not matter, but usually it will – gbn Jul 12 '13 at 13:22
4

Option 2 actually counts all the fields where some_column_name is not null. Option 1 counts all the fields where any field is not null. So you might actually get different results out of these two queries. Most of the time you actually want to count all the rows, and then the fastest option, which does not check for any of the fields, is simply SELECT COUNT(1) FROM ...

David Hedlund
  • 128,221
  • 31
  • 203
  • 222
  • Count(*) also counts ALL rows, not just rows where any value is not null. And in terms of performance, Count(*) and Count(1) is the same, since the query optimizer recognizes what you are trying to do and generates the same execution plan. – Maximilian Mayerl Nov 17 '09 at 10:56
1

No, there is no performance gain in Sql Server.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284