Understanding COUNT(*), COUNT(1), and COUNT(column) in SQL Server

Today we Provides an in-depth insight of the various variations of the COUNT function in SQL Server, namely, COUNT(*), COUNT(1) , and COUNT(column). It describes their functions, distinctions, and performances with real-life illustrations using example tables and data.

SQL Server COUNT introduction

The SQL COUNT function returns the number of items in a group. It is normally used in establishing the number of rows in a table or the number of rows that meet a certain criterion. The performance of COUNT however, depends on the argument passed as an input. Now, we can take a look at the variations available:

1.COUNT(*)

COUNT(*)returns the total number of rows in a table and this does not take into consideration NULL values in any of the columns. It merely enumerates the number of rows that are present.

Example:

First, let's create a sample table named Employees:

CREATE TABLE Employees (  
    EmployeeID INT PRIMARY KEY,  
    FirstName VARCHAR(50),  
    LastName VARCHAR(50),  
    Department VARCHAR(50),  
    Salary DECIMAL(10, 2)  
);  

--data  
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) VALUES  
(1, 'John', 'Doe', 'Sales', 50000.00),  
(2, 'Jane', 'Smith', 'Marketing', 60000.00),  
(3, 'Robert', 'Jones', 'Sales', 55000.00),  
(4, 'Michael', 'Brown', 'IT', 70000.00),  
(5, 'Linda', 'Davis', 'Marketing', 62000.00);  

--Table output

EmployeeID  FirstName                      LastName                       Department                     Salary  
----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------  
1           John                           Doe                            Sales                          50000.00  
2           Jane                           Smith                          Marketing                      60000.00  
3           Robert                         Jones                          Sales                          55000.00  
4           Michael                        Brown                          IT                             70000.00  
5           Linda                          Davis                          Marketing                      62000.00  

--query

select COUNT(*) as TotalEmployees from Employees

    
--output    
    
TotalEmployees    
--------------    
5    
    
(1 row affected)    

COUNT(*) = 5 which is the total row number in Employees table.

2.COUNT(1)

COUNT(1) also returns the total number of the rows in a given table just like in COUNT(*) case. The value in the parentheses is just any constant value. SQL Server is never looking at the value, it is merely counting rows. In effect, it simply evaluates the expression 1 (which is never NULL) and counts it, per row.

Example:

Using the same Employees table, let's use COUNT(1):

--query
SELECT COUNT(1) AS TotalEmployees
FROM Employees;
--output
TotalEmployees
--------------
5
(1 row affected)

The results are identical to COUNT(*) In the majority of circumstances, COUNT(1) and COUNT(*) perform essentially the same.

3.COUNT(column)

COUNT(column) returns the number of rows, which do not have NULL in the column. It disregards rows in which the column has a NULL value.

Example:

Let's modify the Employees table to include a column that can contain NULL values:

ALTER TABLE Employees  
ADD PhoneNumber VARCHAR(20) NULL;  
--  
UPDATE Employees  
SET PhoneNumber = '555-123-4567'  
WHERE EmployeeID IN (1, 2, 4);  
--  
UPDATE Employees  
SET PhoneNumber = NULL  
WHERE EmployeeID IN (3, 5);  

Now, the Employees table looks like this:

--data  
EmployeeID  FirstName                      LastName                       Department                     Salary                         PhoneNumber  
----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------  
1           John                           Doe                            Sales                          50000.00                       555-123-4567  
2           Jane                           Smith                          Marketing                      60000.00                       555-123-4567  
3           Robert                         Jones                          Sales                          55000.00                       NULL  
4           Michael                        Brown                          IT                             70000.00                       555-123-4567  
5           Linda                          Davis                          Marketing                      62000.00                       NULL  
  
(5 rows affected)  

--query  
SELECT COUNT(PhoneNumber) AS EmployeesWithPhoneNumber  
FROM Employees;  
  
--output  
  
EmployeesWithPhoneNumber  
------------------------  
3  
  
(1 row affected)  

COUNT(PhoneNumber) returned 3 because only 3 employees have a non-NULL value in the PhoneNumber column.

Key Differences

| Function | Description | Ignores NULLs |

COUNT(*) : Returns the total number of rows in the table. | No
COUNT(1) : Returns the total number of rows in the table. | No
COUNT(column) : Returns the number of rows where the specified column is not NULL. | Yes

Performance Considerations

The difference in the performance between COUNT(*) and COUNT(1) is, in most cases, insignificant. The query optimizer in SQL Server normally knows how to treat them equally. COUNT(column) can also be a bit slower, in case the column is not indexed, since it would have to see each value to see if it is NULL.

To count all of the rows in a table, it is normally preferred to use COUNT(*)or COUNT(1) . In case you need to count only these rows where a certain column is not NULL, then COUNT(column) is the right solution.

Use Cases Cases

COUNT(*) or COUNT(1): Finding out the total count of customers or orders or products or any other entity which is represented by a table.

Or COUNT(column): Locating the number of customers that have submitted a valid email address (where the email address field may contain NULL), the number of products that carry a particular weight (where the weight field may contain NULL where the weight is not known), or the number of employees who have a performance review in file.

Conclusion

It is important to understand the difference between COUNT(*), COUNT(1) , and COUNT(column) when writing an effective, error-free SQL query. When to use which of these variations lies in whether or not you require that all rows be counted or just those that have non-NULL values in a given column. Although the COUNT(*) and COUNT(1) functions are usually interchangeable because they count all rows, COUNT(column) allows one to count only non-NULL values, a functionality that is necessary in numerous practical applications.