NashTech Blog

Get in touch

The difference between COUNT(*), COUNT(1), and COUNT(column_name) in SQL Server

Picture of Hai Tran Minh

Hai Tran Minh

I'm a passionate Technical Lead at NashTech with a strong focus on building scalable, high-performance systems and leading engineering teams toward delivering impactful solutions. With years of experience in software development and architecture, I thrive at the intersection of technology and leadership - transforming complex challenges into elegant, efficient solutions.
Table of Contents

As a SQL user, you’re probably quite familiar with the COUNT() function. Even though it’s relatively simple, it can be used in several different ways. Each way has a very different use. You might see T-SQL code using COUNT(*) or COUNT(1) or COUNT(column_name) or COUNT(DISTINCT column_name).

For the demonstration purpose, I have the following [tmp].[Users] table:

ID FirstName MiddleName LastName
1 Martine P Davies
2 Carolyn NULL Norris
3 Alfred K Hogg
4 Mervyn L Blackburn
5 Dora M Parkin

The records show that all Users except row #2 have a middle name is NULL.


1. COUNT(*)

The COUNT(*) returns the total number of rows in a table, including the NULL values.

Users table returns 5 that shows the total records in that table:

SELECT COUNT(*) as records FROM [tmp].[Users]

2. COUNT(1)

The COUNT(1) function replaces all records from the query result set with value 1. If you have NULL values, it is also replaced by 1. Therefore, COUNT(1) also returns the total number of records (including NULLs) in the table.

SELECT COUNT(1) as records FROM [tmp].[Users]

3. COUNT(column_name)

If we specify a column name in the SQL COUNT function argument, it counts the total number of rows in the table and excludes the NULL in the specified column.

Let’s use COUNT() function with [FirstName], [MiddleName] and [LastName] column:

SELECT COUNT(FirstName) as records FROM [tmp].[Users];
SELECT COUNT(MiddleName) as records FROM [tmp].[Users];
SELECT COUNT(LastName) as records FROM [tmp].[Users];

4. COUNT(DISTINCT column_name)

Assume we have another user whose first name is similar to other. In the below screenshot, look at the row with ID #3 and #6

The DISTINCT() function returns the output as 5 because it considers only one record from the [FirstName] column with value Alfred

5. Conclusion

COUNT(*) counts all rows, including NULLs.
COUNT(1) is the same as COUNT(*), returns counts all the rows, including NULLs.
COUNT(column_name) counts all the rows but non-NULLs only in the specified column.

Scroll to Top