Count(*) vs Count(1) in SQL Server

In SQL Server, the COUNT() function is used to return the number of rows in a query result. There are different ways to use the COUNT() function such as COUNT(*) and COUNT(1). Although they produce the same result, there are subtle differences in how they work internally.

In this article, we will try to understand the difference between Count(*) vs Count(1) in SQL Server.

COUNT() Function in SQL

The COUNT() function in SQL is a powerful tool used to count the number of rows that meet a specified condition in a query. It plays a crucial role in tasks such as aggregation, filtering, and data analysis. There are two primary variations of the COUNT() function:

1. COUNT(*): Counts all rows, including those with NULL values.
   
2. COUNT(1): Counts all rows as well, but does not evaluate any column's NULL values since 1 is a constant.

1. COUNT(*)

It counts every row in the table, regardless of whether the row contains NULL values or not. This is the most commonly used form of COUNT( ).

Syntax:

SELECT COUNT(*) FROM table_name;

Explanation: When you use COUNT(*), SQL Server evaluates all rows and does not reference any specific column. It scans the entire table or the subset defined by a WHERE clause.

2. COUNT(1)

The COUNT(1) also counts all rows in the table, just like COUNT(*), and ignores any NULL values. Since 1 is a constant and not associated with any column, it does not check for NULLs.

Syntax:

SELECT COUNT(1) FROM table_name;

Explanation: When using COUNT(1), SQL Server evaluates each row as "true" because 1 is always a non-null constant. It doesn't reference any specific column, so it doesn't need to inspect the actual data.

Example: Employees Table

The provided SQL queries demonstrate how different counting functions can yield varying results when dealing with NULL values in the Employees table.

1. COUNT(*) counts all rows in the table, including those with NULL values resulting in a total of 4 rows.
   
2. COUNT(EmployeeName) only counts the rows where the EmployeeName column is not NULL, which results in 2 rows.

This illustrates how COUNT specifically excludes NULL entries while COUNT(*) includes all rows regardless of their contents.

Query:

 -- Create Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    DepartmentID INT
);

-- Insert sample data with NULL values
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID)
VALUES 
(101, 'John Doe', 1),
(102, NULL, 2),
(103, 'Sam Green', 3),
(104, NULL, 4);

-- Using COUNT(*): Counts all rows, including rows with NULL values
SELECT COUNT(*) AS TotalRows FROM Employees;

-- Using COUNT(EmployeeName): Counts only rows where EmployeeName is NOT NULL
SELECT COUNT(EmployeeName) AS NonNullNames FROM Employees;

Output:

Explanation:

• COUNT(*) counts all rows, including rows with NULL values.
  
• COUNT(EmployeeName) counts only rows where EmployeeName is not NULL. Thus, COUNT(EmployeeName) produces a different result by excluding rows with NULL in the EmployeeName column.

Difference Between COUNT(*) and COUNT(1)

Conclusion

Both COUNT(*) and COUNT(1) return the same result and perform similarly in modern SQL Server environments, as the SQL optimizer treats them equally. Historically COUNT(1) was sometimes thought to be faster, but there is no significant performance difference in recent versions of SQL Server. Most developers prefer COUNT(*) because it is more intuitive and clearly signifies counting all rows in a table.