Usage of SQL Server Count() Function

The COUNT() function returns the number of rows that match the specified criteria. In short, we use the built-in COUNT() function to count records in a database.

It counts the number of rows that meet the criteria defined in parentheses. It does not return the rows themselves; it only returns the number of rows that match your criteria.

But did you know that there are different variations of SQL COUNT()? In this article, we will look at the most common uses of COUNT() and explain the differences between them.

COUNT(*) vs COUNT(1)

You may have seen discussions about the difference between COUNT(*) and COUNT(1). If you ask whether there is a result difference between them, the answer is simple: no, there is no difference in the returned count.

COUNT(*) counts the total number of rows in the table, including rows that contain NULL values.

COUNT(1) also returns the total number of rows. It evaluates each row using the constant value 1, so rows with NULL values are still included in the count.

As a result, COUNT(*) and COUNT(1) return the same number of rows.

Example Table

Let’s test this with a simple example. Suppose we have a temporary table named #office with 11 records. The first 10 records have a value in the name column, while the last record has a NULL value.

CREATE TABLE #office (
    id INT,
    name VARCHAR(50)
);

INSERT INTO #office (id, name)
VALUES
    (1, 'Office 1'),
    (2, 'Office 2'),
    (3, 'Office 3'),
    (4, 'Office 4'),
    (5, 'Office 5'),
    (6, 'Office 6'),
    (7, 'Office 7'),
    (8, 'Office 8'),
    (9, 'Office 9'),
    (10, 'Office 10'),
    (11, NULL);

If we query the table, we can see that there are 11 rows in total.

SELECT *
FROM #office;

Using COUNT(*)

When * is used inside the COUNT() function, SQL Server counts all rows, including rows that contain NULL values.

In this example, because the table has 11 rows, COUNT(*) returns 11.

SELECT COUNT(*) AS total_rows
FROM #office;

Using COUNT(1)

There is a common misconception that COUNT(1) counts records from the first column. This is not correct.

COUNT(1) counts rows by evaluating the constant value 1 for each row in the result set. Even if a row contains NULL values in one or more columns, the row is still counted.

To understand this more clearly, look at the following query:

SELECT 1
FROM #office;

This query returns the value 1 for every row in the table. Since the table has 11 rows, the result contains 11 rows of 1.

Therefore, when we use COUNT(1), SQL Server still returns 11.

SELECT COUNT(1) AS total_rows
FROM #office;

We can also compare COUNT(*) and COUNT(1) in the same query:

SELECT
    COUNT(*) AS count_star,
    COUNT(1) AS count_one
FROM #office;

Both expressions return the same result because both count all rows in the table.

What Does COUNT(column_name) Do?

When a column name is used inside the COUNT() function, SQL Server counts only the rows where that column is not NULL.

In our example, the id column has 11 non-null values, so COUNT(id) returns 11.

SELECT COUNT(id) AS id_count
FROM #office;

However, the name column has one NULL value. Because COUNT(column_name) ignores NULL values, COUNT(name) returns 10.

SELECT COUNT(name) AS name_count
FROM #office;

We can also see all three variations together:

SELECT
    COUNT(*) AS count_star,
    COUNT(1) AS count_one,
    COUNT(id) AS count_id,
    COUNT(name) AS count_name
FROM #office;

Is There a Performance Difference Between COUNT(*) and COUNT(1)?

In most SQL Server scenarios, COUNT(*) and COUNT(1) are optimized in the same way and return the same result. For this reason, they are generally interchangeable.

The important difference is not between COUNT(*) and COUNT(1). The important difference is between counting all rows and counting only non-null values in a specific column.

Summary

To summarize:

• COUNT(*) counts all rows, including rows with NULL values.
• COUNT(1) counts all rows, including rows with NULL values.
• COUNT(column_name) counts only the rows where the specified column is not NULL.

If you want to count all rows in a table, you can use either COUNT(*) or COUNT(1). If you want to count only rows where a specific column has a value, use COUNT(column_name).