Introduction

In SQL Server, COUNT(*) and COUNT(1) are functionally equivalent for counting rows. The long-running debate about one being faster than the other is mostly folklore. In practice, the more useful comparison is usually COUNT(*) versus COUNT(column).

What COUNT(*) Means

COUNT(*) counts rows returned by the query:

SELECT COUNT(*)
FROM Employees;

It does not care which columns are null because it is counting rows, not values from a particular column.

This is also true with filters and joins. COUNT(*) simply counts the rows in the result set after the FROM, JOIN, and WHERE logic is applied.

What COUNT(1) Means

COUNT(1) counts a constant non-null expression for each row:

SELECT COUNT(1)
FROM Employees;

Because the expression 1 is non-null for every row, the result is the same as COUNT(*).

In SQL Server, the optimizer understands this. For ordinary row-counting queries, COUNT(*) and COUNT(1) produce the same result and are typically optimized the same way.

Where People Get Confused

The real difference is not between COUNT(*) and COUNT(1). It is between those forms and COUNT(column):

SELECT COUNT(ManagerId)
FROM Employees;

This counts only the rows where ManagerId is not null. That is a different question.

Example:

1CREATE TABLE Demo (
2    Id INT,
3    Value INT NULL
4);
5
6INSERT INTO Demo (Id, Value)
7VALUES (1, 10), (2, NULL), (3, 30);
8
9SELECT COUNT(*) AS AllRows,
10       COUNT(1) AS CountOne,
11       COUNT(Value) AS NonNullValueRows
12FROM Demo;

Expected result:

• 'COUNT(*) = 3'
• 'COUNT(1) = 3'
• 'COUNT(Value) = 2'

That difference matters much more than the * versus 1 style debate.

Which Form Should You Write

In SQL Server code, COUNT(*) is usually the clearest choice when you mean “count rows.” It states your intention directly and is the most widely recognized convention.

SELECT COUNT(*)
FROM Orders
WHERE Status = 'Open';

There is no practical advantage to rewriting that as COUNT(1).

Counting in Joins and Groups

The same guidance applies in grouped queries:

SELECT DepartmentId, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentId;

That counts rows per department. If you instead write COUNT(EmailAddress), then departments with null email values will be counted differently.

So when reviewing grouped SQL, ask “am I counting rows or non-null values from this column?” That is the decision that matters.

Performance Myths

You will sometimes hear claims that COUNT(1) is faster because SQL Server does not need to expand *. In modern SQL Server, that is not how the query should be understood. The optimizer does not literally iterate over every column because of the asterisk.

For normal row counts, both forms are optimized to the same plan shape in ordinary scenarios. If a query is slow, the cause is far more likely to be:

• table size
• predicates
• indexing
• join shape
• blocking or concurrency

Switching COUNT(*) to COUNT(1) is not the fix.

A Related Tool: COUNT_BIG

If you may exceed the integer range of COUNT, SQL Server also offers COUNT_BIG:

SELECT COUNT_BIG(*)
FROM VeryLargeTable;

This is not about speed either. It is about the size of the return type.

Common Pitfalls

The most common mistake is assuming COUNT(column) behaves like COUNT(*). It does not. Nulls are excluded.

Another pitfall is spending time micro-optimizing COUNT(*) versus COUNT(1) while ignoring much larger query design issues.

Developers also sometimes choose COUNT(1) because they think it signals better performance. In SQL Server, it mostly signals style preference.

Finally, when joins duplicate rows, COUNT(*) counts those duplicate joined rows. If you really need distinct entities, use COUNT(DISTINCT ...) where supported by the query design.

Summary

• In SQL Server, COUNT(*) and COUNT(1) are effectively equivalent for row counting.
• 'COUNT(*) is usually the clearest and most conventional form.'
• 'COUNT(column) is different because it ignores null values in that column.'
• Performance differences between COUNT(*) and COUNT(1) are generally not meaningful in practice.
• Focus on query intent and execution plan quality, not folklore about the asterisk.