In this lesson, you will learn how to leverage pre-sorted index structures to calculate distinct counts without triggering expensive "Sort" operations.
The Problem with Distinctness
When you run a query like SELECT COUNT(DISTINCT CategoryID) FROM Products, the database engine often faces a heavy lifting task. By default, it must group all the values together to identify the unique ones. If the data isn't pre-sorted, the engine creates a "Sort" operation in memory (or spills to TempDB if the memory limit is exceeded). As we learned in our previous sessions on the Cost of Sort Operations, this is a major performance bottleneck that scales poorly as your table grows.
The Index Advantage
If you have an index on the column you are aggregating (e.g., CREATE INDEX idx_category ON Products(CategoryID)), the data is already stored in a logical, sorted order on disk.
Because the values are already aligned, the database engine can perform an Index-Based Aggregation (sometimes called a "Stream Aggregate"). Instead of sorting, the engine simply performs an index scan. It walks through the index leaf nodes and increments a counter every time it hits a new value. Since the index is already sorted, it knows exactly when a value changes, allowing it to calculate the count in a single pass without needing a memory-intensive sort buffer.
Visualizing the Efficiency
Imagine trying to count unique items in a messy pile versus counting unique items in a list that is already sorted alphabetically:
-- Without an index:
-- Engine sorts all CategoryID values, then removes duplicates. (High Cost)
-- With an index on CategoryID:
SELECT COUNT(DISTINCT CategoryID)
FROM Products;
-- Engine reads the index leaf nodes sequentially.
-- It identifies a change in value as it moves, counting unique instances on the fly. (Low Cost)
By relying on the index's existing structure, you bypass the need for an "External Sort" entirely. This optimization transforms a resource-heavy operation into a lean, predictable traversal of the index tree.
Key Takeaway: By using an index on the column being evaluated for distinctness, the engine can use a Stream Aggregate to count unique values in a single sequential pass, completely eliminating the need for a costly sort operation.