Performance of COUNT SQL function

Question

Hi All,

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

1. SELECT COUNT(*) FROM
2. SELECT COUNT(some_column_name) FROM

In terms of performance what is the beast SQL statement? can I obtain some performance gain by using option: 1 ?

Thanks, Upul

Answer 1

No, there is no performance gain in Sql Server.

Answer 2

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 ...

Answer 3

It does 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 "Count(*) vs Count(1)" question

Answer 4

You can visit a similar question..

StackOverflow

Hope this helps you... :-)