Performance of COUNT SQL function

问题:

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?

回答1:

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

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

回答3:

No, there is no performance gain in Sql Server.