In this lesson, you will learn how to structure composite indexes to enable the database engine to perform aggregations without relying on expensive, memory-heavy hash operations.
Avoiding the "Hash" Trap
When you run a GROUP BY query, the database needs to organize your data into buckets based on the specified columns. By default, if the data isn't pre-sorted or indexed, the engine often builds a Hash Table in memory (or spills it to TempDB if it’s too large) to group these values. This "hash-based aggregation" is computationally expensive.
However, if your data is already stored in an order that matches your GROUP BY clause, the database can perform a Stream Aggregate. This is significantly faster because it simply walks through the index sequentially, "resetting" the counter every time it hits a new group key.
Aligning Composite Indexes
To enable this, your composite index must mirror the grouping requirements. If you have a query like this:
SELECT Category, Region, SUM(Sales)
FROM Orders
GROUP BY Category, Region;
A standard index on (Category) isn't enough; the engine still has to hash the Region values. Instead, you should create a composite index that covers the hierarchy of the group:
CREATE INDEX IX_Orders_Category_Region
ON Orders (Category, Region);
Because the index stores data physically sorted by Category first, and then by Region within each category, the database can stream the results directly from the index tree. It treats the index as a pre-sorted map, avoiding the need to allocate memory for a hash table or perform an external sort.
When does this fail?
If you add a third column to your GROUP BY that isn't in your index, or if you change the order of columns in the GROUP BY clause to something that doesn't match the index prefix (e.g., GROUP BY Region, Category), the engine may be forced to revert to hash-based aggregation because the physical order no longer guarantees the required grouping sequence. Always align your index columns with the logic of your GROUP BY clause to keep the operation efficient.
Key Takeaway: By aligning your composite indexes with your GROUP BY column order, you allow the engine to use a high-performance stream aggregate, bypassing the heavy overhead of hash tables.