SQL COUNT() Reference: Syntax, Performance & Troubleshooting

sql query count rows is the SQL Server T-SQL operation to return the number of rows in a table or result set, using COUNT(*), COUNT(column), or COUNT(DISTINCT expression).

SELECT COUNT(*) FROM table_name;

What is COUNT and when to use it?

COUNT is an aggregate function that counts rows satisfying the query predicate. COUNT(*) returns the total number of rows, including duplicates and NULLs. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values.

Tested on SQL Server 2022 (16.0) with AdventureWorks2022 sample database.

Syntax

-- Full syntax diagram for COUNT
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
       [ OVER ( [ partition_by_clause ] [ order_by_clause ] [ ROW_or_RANGE_clause ] ) ]

-- Common forms:
SELECT COUNT(*) FROM table_name;                     -- all rows
SELECT COUNT(ALL expression) FROM table_name;        -- non-NULL values (ALL is default)
SELECT COUNT(DISTINCT expression) FROM table_name;   -- unique non-NULL values
SELECT COUNT(*) OVER (PARTITION BY column) ...       -- running count within partitions

Usage Examples

1. Basic row count with WHERE filter

-- Count orders with status 'Shipped'
SELECT COUNT(*) AS ShippedCount
FROM Sales.SalesOrderHeader
WHERE Status = 5;

Scans the clustered index of the table, filtering on the Status column. For large tables, ensure an index on Status INCLUDE (primary key) to avoid full scan.

2. Count distinct product IDs per territory

-- How many different products were ordered in each territory?
SELECT TerritoryID, COUNT(DISTINCT ProductID) AS UniqueProducts
FROM Sales.SalesOrderDetail
GROUP BY TerritoryID;

This eliminates duplicate ProductID values per group. Performance depends on the cardinality of ProductID; consider a filtered index if this query runs frequently.

3. Running row count with OVER

-- Cumulative count of orders per customer, ordered by order date
SELECT CustomerID, OrderDate,
       COUNT(*) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS OrderSeq
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;

Each row shows the number of orders up to that date for that customer. The OVER clause uses RANGE UNBOUNDED PRECEDING by default when ORDER BY is specified.

Error Codes and Troubleshooting

Arguments

Return types

COUNT returns int. COUNT_BIG returns bigint (use when row count may exceed 2,147,483,647).

Performance Considerations

For production row counts on large tables, prefer sys.dm_db_partition_stats over a full COUNT(*) scan. Validate accuracy by comparing against a full count during off-peak hours once per maintenance window. Use COUNT_BIG if row count exceeds int range (Msg 8115). Filter NULLs explicitly to avoid ANSI_WARNINGS errors. For COUNT(DISTINCT), create a non-clustered index on the column to reduce memory grants.

Frequently Asked Questions

SELECT COUNT(*) AS total_rows,
       COUNT(email) AS emails_provided,
       COUNT(DISTINCT department) AS distinct_depts
FROM employees;

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- Wrong:
SELECT department, name, COUNT(*) FROM employees GROUP BY department;
-- Fix (if you want name per group, use MIN/MAX):
SELECT department, MIN(name) AS sample_name, COUNT(*) FROM employees GROUP BY department;

SELECT COUNT(*) FROM orders WHERE status = 'shipped';

SELECT COUNT(*) FROM large_table FORCE INDEX (PRIMARY);