Databases Reference
In-Depth Information
If we create an index, the Query Optimizer will introduce a Stream Aggregate operator
since the plan can take advantage of the fact that the data is already sorted. To test it,
run this:
CREATE
INDEX
IX_Title
ON
HumanResources
.
Employee
(
Title
)
Listing 2-24.
Then run the previous queries again. Both queries will now produce the plan shown
on Figure 2-21.
Figure 2-21:
A Stream Aggregate used by a query with a
DISTINCT
keyword.
Drop the index before continuing, by using this statement:
DROP
INDEX
HumanResources
.
Employee
.
IX_Title
Listing 2-25.
Finally, for a bigger table without an index to provide order, a Hash Aggregate may be
used, as in the two following examples.