Databases Reference
In-Depth Information
Figure 2-19:
A Hash Aggregate followed by a Sort operator.
Finally, a query using the
DISTINCT
keyword can be implemented by a Stream Aggregate,
a Hash Aggregate or by a Distinct Sort operator. The Distinct Sort operator is used
to both remove duplicates and sort its input. In fact, a query using
DISTINCT
can be
rewritten as a
GROUP
BY
query, and both can generate the same execution plan. If an
index to provide sorted data is available, the Query Optimizer can use a Stream Aggregate
operator. If no index is available, SQL Server can introduce a Distinct Sort operator or a
Hash Aggregate operator. Let's see all three cases here; the following two queries return
the same data and produce the same execution plan, as shown in Figure 2-20.
SELECT
DISTINCT
(
Title
)
FROM
HumanResources
.
Employee
SELECT
Title
FROM
HumanResources
.
Employee
GROUP
BY
Title
Listing 2-23.
Note that the plan is using a Distinct Sort operator. This operator will sort the rows and
eliminate duplicates.
Figure 2-20:
A Distinct Sort operator.
