3

Structure of Example table:

Id, Integer (PK)
Name, Varchar(100)
Description, Text

I need to know if exists difference in performance between:

SELECT COUNT(*) FROM Example;

and

SELECT COUNT(Id) FROM Example;

Or does not exists differences?

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Cesar
  • 3,519
  • 2
  • 29
  • 43

4 Answers4

2

Differences exist in both performance and the actual resultset

Performance of COUNT SQL function

Community
  • 1
  • 1
David Hedlund
  • 128,221
  • 31
  • 203
  • 222
  • 1
    since id is the PK, it is (hopefully!) unlikely it will contain NULLs, which I think is the main difference in results. – davek Nov 17 '09 at 13:25
  • Thank you, I did not find this question. – Cesar Nov 17 '09 at 13:27
  • @davek - Since `id` is the PK it is not permitted for it to contain nulls. This answer is incorrect. Performance and resultset will be the same. – Martin Smith Oct 22 '12 at 10:34
0

Check the explain plan: does the result come from the table or from meta data (e.g. INFORMATION_SCHEMA)?: The differences are likely to be very minimal.

davek
  • 22,499
  • 9
  • 75
  • 95
0

I would believe that there is a difference; depending on the size of your table, it could be negligible, or it could be significant.

This is probably the slowest: SELECT COUNT(*) FROM Example
This is in the middle: SELECT COUNT(Id) FROM Example
This would be fastest: SELECT COUNT(1) FROM Example

Jim B
  • 8,344
  • 10
  • 49
  • 77
  • Also depends on what you want to count. Id will probably never be null (if you're doing it right), but count(*) will count only rows with no nulls for example. – tsilb Nov 17 '09 at 13:42
0

Worth reading, but unfortunately in russian.

COUNT(*), could it be faster?

Short story is that COUNT(*) is not so efficient as direct access to the dm_db_partition_stats.

Mike Chaliy
  • 25,801
  • 18
  • 67
  • 105