SQL performance: Count(*) vs. Count(1)

Counting the number of records in a table or query is a pretty common task for developers. To count the amount of rows returned in a recordset, the syntax is:

SELECT Count(*) FROM tablename

The code is simple enough, but some people look at the * (asterik) and feel uncomfortable. A developer is always looking for ways to improve performance; it’s understandable to think you can get a performance boost with the following code:

SELECT Count(1) FROM tablename

After all, the asterik is usually used to select all columns in a table, and one should never select columns which are not going to be used. So why select Count(*)?

There are 2 reasons you should always use Count(*) rather than Count(1):

1. Performance

SQL Server Query Optimizer interprets the Count(*) differently from SELECT *. So when it sees Count(*) it automatically follows the quickest execution plan. Using Count(1) may cause the query optimizer fail and thus slow down your application.

2. Results

Count(*) will count all the records in the recordset, while Count(1) will count all non-NULL records in the recordset. So in some rare cases, Count(1) may give you unexpected results.

Conclusion

Always use Count(*) when counting a recordset!