Count(*) vs. Count(1) in SQL Server: Does it Impact Performance?

*Performance difference between COUNT() and COUNT(1) in SQL Server: in-depth analysis**

Many developers are concerned about whether using COUNT(1) is better than COUNT(*) in SQL Server 2005. This article will delve into this issue and explain the reasons behind it.

Conclusion: No difference in performance

Contrary to popular belief, there is no significant performance difference between COUNT(1) and COUNT(*). Both produce the same result in terms of counting rows.

Cause: SQL Server’s optimizer

SQL Server’s optimizer recognizes that both COUNT(1) and COUNT(*) return the same information and optimizes accordingly. The optimizer treats these two functions as trivial operations, which means that the evaluation of the expression is extremely simple and does not affect performance.

Example

Consider the following query:

<code class="language-sql">SELECT COUNT(1) FROM dbo.tab800krows
SELECT COUNT(1), FKID FROM dbo.tab800krows GROUP BY FKID

SELECT COUNT(*) FROM dbo.tab800krows
SELECT COUNT(*), FKID FROM dbo.tab800krows GROUP BY FKID</code>

The input/output (IO) and execution plans are identical for both sets of queries. This proves that the SQL Server optimizer recognizes the equivalence of COUNT(1) and COUNT(*).

Summary

In summary, using COUNT(1) does not provide any performance advantage over COUNT(*) in SQL Server 2005. The database system optimizes both functions to produce the same results. Therefore, developers can choose the syntax they prefer without worrying about performance issues.

The above is the detailed content of Count(*) vs. Count(1) in SQL Server: Does it Impact Performance?. For more information, please follow other related articles on the PHP Chinese website!