Databases Reference
In-Depth Information
On the other hand, hashing is used by the Hash Aggregate and Hash Join operators, both
of which work by building a hash table in memory. The Hash Join operator uses memory
only for the smaller of its two inputs, which is defined by the Query Optimizer.
Sorting also uses memory and, similar to hashing, will also use the
tempdb
database if
there is not enough available memory, which could become a performance problem. Both
sorting and hashing (only during the time the build input is hashed, as explained later) are
blocking or stop-and-go operations; that is, they cannot produce any rows until they have
consumed all their input.
Stream Aggregate
Let us start with the Stream Aggregate operator, using a query with an aggregation
function. Queries using an aggregate function and no GROUP BY clause are called
scalar aggregates
, as they return a single value, and are always implemented by the
Stream Aggregate operator. To demonstrate, run the following query, which shows the
plan in Figure 2-12:
SELECT
AVG
(
ListPrice
)
FROM
Production
.
Product
Listing 2-15.
Figure 2-12:
A Stream Aggregate.
