A Comprehensive Description of COUNT in SQL Server

Counting rows is a frequent task that many SQL developers come into when working with SQL Server. It is essential to comprehend the distinctions between DISTINCT COUNT, COUNT(1), and COUNT(*) when crafting effective and optimized queries. We will delve further into these SQL counting techniques in this post, comprehend their subtleties, and look at performance issues.

COUNT(*)

The COUNT(*) function is used to count all rows in a table or the result set of a query. It includes NULL values and duplicates. This is the most commonly used COUNT function due to its simplicity and broad applicability.

USE [AdventureWorks2022]
GO

SELECT COUNT(*) AS TotalRows
FROM [HumanResources].[Employee] WITH(NOLOCK)

COUNT(1)

The COUNT(1) function operates similarly to COUNT(*) but counts the number of rows by evaluating the constant value 1 for each row. This can sometimes be seen as a trick to improve performance, although in most cases, there is no significant difference.

USE [AdventureWorks2022]
GO

SELECT COUNT(1) AS TotalRows
FROM [HumanResources].[Employee] WITH(NOLOCK)

COUNT(column_name)

The COUNT(column_name) function counts the number of non-NULL values in a specified column. This is useful when you need to count entries in a column while ignoring NULL values.

USE [AdventureWorks2022]
GO

SELECT COUNT(BusinessEntityID) AS TotalRows
FROM [HumanResources].[Employee] WITH(NOLOCK)

COUNT(DISTINCT column_name)

The COUNT(DISTINCT column_name) function counts the number of distinct non-NULL values in a specified column. This is useful for identifying the number of unique entries in a dataset.

USE [AdventureWorks2022]
GO

SELECT COUNT(LoginID) AS TotalRows
FROM [HumanResources].[Employee] WITH(NOLOCK)

SQL

Performance Considerations
 

COUNT(*)	COUNT(1)	COUNT(column_name)	COUNT(DISTINCT column_name)
The COUNT(*) function is efficient because it does not need to evaluate any specific column values. Instead, it counts rows at the storage level. SQL Server optimizes this operation by utilizing the table's metadata, making it faster than counting specific columns, especially when those columns are large or contain complex data types.	From a performance perspective, COUNT(1) and COUNT(*) are typically equivalent. The SQL Server optimizer treats them the same way and generates similar execution plans. Therefore, choosing between COUNT(1) and COUNT(*) is mostly a matter of preference or coding standards rather than performance.	COUNT(column_name) can be less efficient than COUNT(*) or COUNT(1) because it requires evaluating each value in the specified column to determine if it is NULL. However, if the column is indexed, SQL Server can leverage the index to improve performance. It is important to consider the size and complexity of the column when using this function.	COUNT(DISTINCT column_name) can be significantly more resource-intensive than COUNT(*) or COUNT(column_name) because it requires sorting and deduplicating the values in the specified column. The performance impact is more pronounced for large datasets or columns with many distinct values. Using indexes on the column can help, but it may not completely mitigate the overhead.

Use Cases

COUNT(*)	COUNT(1)	COUNT(column_name)	COUNT(DISTINCT column_name)
Counting all rows in a table or result set	Similar to COUNT(*), often used interchangeably	Counting non-NULL values in a specific column	Counting unique non-NULL values in a specific column

Advanced Scenarios

• Counting with Conditions: Sometimes, you may need to count rows based on specific conditions. This can be achieved using the CASE statement within the COUNT function.

  USE [AdventureWorks2022]
  GO
  
  SELECT
      COUNT(CASE WHEN OrganizationLevel = 1 THEN 1 END) AS VicePresident,
      COUNT(CASE WHEN OrganizationLevel = 2 THEN 1 END) AS EngineeringManager
  FROM [HumanResources].[Employee] WITH(NOLOCK)

Combining COUNT with Other Aggregate Functions: You can combine COUNT with other aggregate functions like SUM, AVG, MAX, and MIN to derive more complex insights.

USE [AdventureWorks2022]
GO

SELECT
    COUNT(*) AS TotalEmployees,
    AVG(Rate) AS AverageSalary,
    MAX(Rate) AS HighestSalary,
    MIN(Rate) AS LowestSalary
FROM [HumanResources].[EmployeePayHistory] WITH(NOLOCK)

Conclusion

Understanding the differences between COUNT(*), COUNT(1), COUNT(column_name), and COUNT(DISTINCT column_name) is crucial for SQL developers, data engineers, and DBAs. Each function serves specific purposes and has unique performance characteristics. By selecting the appropriate COUNT function and optimizing your queries, you can efficiently derive insights from your data and ensure optimal performance in SQL Server.

SQL Server 2022 Recommendation

HostForLIFEASP.NET receives Spotlight standing advantage award for providing recommended, cheap and fast ecommerce Hosting including the latest SQL Server 2022 Hosting. From the leading technology company, Microsoft. All the servers are equipped with the newest Windows Server 2012 R2, SQL Server 2014, ASP.NET 4.5.2, ASP.NET MVC 6.0, Silverlight 5, WebMatrix and Visual Studio Lightswitch. Security and performance are at the core of their Magento hosting operations to confirm every website and/or application hosted on their servers is highly secured and performs at optimum level. mutually of the European ASP.NET hosting suppliers, HostForLIFE guarantees 99.9% uptime and fast loading speed. From €3.49/month , HostForLIFE provides you with unlimited disk space, unlimited domains, unlimited bandwidth,etc, for your website hosting needs.