SQL Server COUNT() and Effective Row Counting

The SQL COUNT function is one of the fundamental SQL functions supported across almost all database management systems. It allows users to efficiently count rows in SQL based on specific criteria, ranging from simple data record counting to more advanced calculations in conjunction with other SQL functions.

With its variety of options, the SQL row count function becomes a useful component of many data-centric applications: reporting, validation, data analytics, and other processes depending on real-time data-driven insights. It accelerates and streamlines data management by automating record counting, validating datasets for missing or incomplete data, identifying unique data entries, and grouping data based on the specified criteria.

In this article, we'll delve into the nuances and applications of the COUNT function in SQL Server to help the readers understand this function's significance and usage.

What is the SQL Server COUNT() function

In SQL Server, the COUNT() function returns the number of table rows according to the definite criteria.

Depending on the scenario, it can count all records, including duplicates, NULLs, non-NULL values, and unique records. It can return 0 if no rows match the criteria.

The function is mostly used with the SELECT statement. The data type it returns is INT.

The COUNT() function is supported in SQL Server, as well as in Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, Analytics Platform System (PDW), SQL Endpoint in Microsoft Fabric, and Warehouse in Microsoft Fabric.

The SQL Server row count function is valuable in the realm of relational databases, as it performs one of the fundamental aggregation operations. It is often used to verify and ensure data integrity, check data quality, and for reporting purposes.

Syntax of the COUNT() function

As mentioned, the SQL COUNT function mostly serves as a part of the SELECT statement. The basic syntax of the query is as follows:

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE condition];

In this syntax:

• aggregate_expression defines for the specific column or expression containing the values to be counted
• tables define the database tables to retrieve values from. You need to specify at least one table in the FROM clause
• WHERE condition is an optional condition that specifies criteria for the records to be selected and counted

The simplest example is counting all records in the specific table column:

SELECT COUNT (Name)
FROM Production.Product;

The result is the number of all non-NULL values in the particular column you specify in the query.

Now, let us examine the usage of the SQL Server COUNT function in database management.

SQL COUNT examples and common use cases

The COUNT function is among the most commonly used aggregate functions in SQL and has a wide range of applications. To demonstrate its use cases and show how to count rows in SQL to achieve the expected results, we'll use dbForge Studio for SQL Server. It is a powerful integrated development environment (IDE) tailored for SQL Server specialists that provides a comprehensive solution for all database-related tasks.

Count all rows in one or multiple tables

If you need the definite number of all rows of the table that may include duplicates, non-null values, and nulls, the COUNT(*) form is the solution:

SELECT COUNT(*) 
FROM tableName;

Assume we need to count all available products in the stock:

SELECT COUNT (*) 
FROM Production.Product;

COUNT(*) can be applied to multiple tables too. The below example demonstrates how to count the number of all employees and all departments at once:

SELECT 
    (SELECT COUNT(*) FROM HumanResources.Employee) AS EmployeeCount,
    (SELECT COUNT(*) FROM HumanResources.Department) AS DepartmentCount;

COUNT() with ALL

COUNT(ALL <expression>) is the specific form used when we need to count the total number of table rows containing non-null values. Here lies the difference between COUNT(*) and COUNT ALL - the asterisk (*) sets the function to count both non-null and null values.

SELECT COUNT(ALL color)
FROM Production.Product;

COUNT() with DISTINCT

When the function is used with the DISTINCT clause, it returns the number of unique non-null values. Therefore, COUNT DISTINCT is helpful whenever you need to eliminate repetitive data in the result set.

SELECT COUNT(DISTINCT columnName) 
FROM tableName;

Assume we want to count the number of products and their available colors. In this case, we can use the SQL COUNT() function with the DISTINCT clause as shown below:

SELECT 
    COUNT(DISTINCT name) AS ProductName,
    COUNT(DISTINCT color) AS ProductColor
FROM Production.Product;

COUNT() with condition

As the purpose of this function by default is fetching the number of table rows according to some condition, COUNT() is frequently used with the WHERE clause:

SELECT COUNT(expression)
FROM table
WHERE condition;

Assume we want to know how many products in stock have prices higher than 1000:

SELECT COUNT(*) as "More Expensive Products "
FROM Production.Product
WHERE ListPrice>1000;

COUNT() with GROUP BY

Grouping allows us to get a better readable result set, and the GROUP BY clause is also common in the queries with the COUNT() function. The standard syntax of the query is as follows:

SELECT expression1, expression2, ... expression_n,
       COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

For instance, we want to find out how many orders were placed by each customer. For that, we can apply the COUNT function to fetch the list of CustomerID values along with the number of orders each customer has placed, sorted by the number of orders.

SELECT CustomerID, COUNT(*) AS NumberOfOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY NumberOfOrders DESC;

COUNT(*) with other aggregate functions

The COUNT function is often combined with other aggregate functions in SQL Server to bring more detailed and precise results. Such combined queries enhance the SQL Server rows count with other computations, returning data tailored to the user's needs.

Assume we want to retrieve the list of product categories along with the number of products in each category and define the average price of the products within each category.

SELECT 
    pc.Name AS CategoryName,
    COUNT(*) AS NumberOfProducts,
    AVG(p.ListPrice) AS AveragePrice
FROM Production.Product p
INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
GROUP BY pc.Name;

SQL Server COUNT () function variants: Comparison table

The following table contains the most common variants of the COUNT function usage with typical use cases.

Best practices for optimal performance

The COUNT function, due to its simplicity and value, is one of the most common SQL functions used in practice. However, to apply it effectively in environments, you should master some best practices. Let us review the most efficient approaches.

The best methods for optimal query performance mostly target making SQL queries faster and lighter. In that aspect, you can use the following recommendations that apply to most use cases.

• Apply COUNT(*), COUNT(column_name), or COUNT DISTINCT correctly:
  • If you only require the number of all rows, use COUNT(*).
  • If you need more specific numbers, like the number of non-NULL values in a specific column, use COUNT(column_name).
  • The COUNT(DISTINCT column_name) option can be more resource-intensive. Make sure that the column you refer to is indexed and consider applying other strategies to retrieve the same values.
• Use indexes.

  Make sure that the column you refer to count rows is indexed, especially if your query includes the WHERE condition. Using indexes helps speed up the query performance and filtering.

• Avoid COUNT() in JOINs.

  If you're joining tables and applying the COUNT function, make sure you really need data from all tables at once. In some cases, referring to a single table can be more efficient.

• Avoid COUNT() in subqueries.

  The COUNT function is correlated subqueries that may cause performance issues, as that type of subquery is quite resource-consuming. If your scenario allows it, redesign the query to apply Common Table Expressions (CTE) or temporary tables to derive the counted values from them instead of subqueries.

• Use partitioning for large tables.

  Limiting the scope is always one of the most effective methods of query optimization. If the target table is very large, but you often need to query it to count rows according to certain criteria, use the table partitioning on those criteria.

• Update statistics regularly.

  Make sure to have the stats always updated for tables and indexes to rely on up-to-date information for making optimal decisions.

• Check execution plans.

  Analyze the execution plans of the COUNT queries and review them periodically. If you use the Query Profiler feature of dbForge Studio for SQL Server, note that it offers the possibility to analyze queries, troubleshoot the issues, detect, and resolve diverse performance troubles.

These best practices, when applied, ensure that your use of the COUNT function in SQL Server is efficient and optimized for your specific needs.

Conclusion

The SQL COUNT function is an indispensable tool within the SQL Server arsenal as it provides insights into the volume and nature of data stored in databases. By mastering the usage and nuances, one can optimize data retrieval operations, generate comprehensive reports, and make informed decisions. However, like many tools in SQL, its true power is unlocked when combined creatively with other SQL functions and clauses.

FAQ

SELECT COUNT(*) as "Shipped orders"
FROM Orders
WHERE Status = 'Shipped';