Count(*) vs. Count(1) in SQL Server: Is There a Performance Difference?

*SQL Server: Count() vs. Count(1) – A Performance Analysis**

Many SQL users wonder about the performance difference between COUNT(*) and COUNT(1). Both functions count rows, but is there a real performance impact?

In SQL Server (specifically, versions from 2005 onwards), the answer is straightforward: no significant performance difference exists.

Microsoft's documentation clarifies that COUNT(*) counts all rows in a table (or those meeting a specified condition). Crucially, COUNT(1)—because '1' is a non-null expression—yields the same result.

The SQL Server query optimizer recognizes this equivalence. It treats both functions identically, resulting in the same execution plan, I/O operations, and ultimately, performance.

Consider these examples:

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

Executing these queries will produce identical results, confirming the functional equivalence of COUNT(*) and COUNT(1). Therefore, the choice between them is largely a matter of coding style or personal preference; it won't impact query performance in SQL Server.

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