Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 299

However, you could also use a derived table like this:
SELECT PurchaseOrderNumber
FROM Sales.SalesOrderHeader h
INNER JOIN (SELECT DISTINCT d.SalesOrderId
FROM Sales.SalesOrderDetail d
WHERE d.CarrierTrackingNumber = N'E257-40A1-A3'
)d
ON h.SalesOrderId = d.SalesOrderId
You could build a correlated subquery like this:
SELECT PurchaseOrderNumber
FROM Sales.SalesOrderHeader h
WHERE EXISTS (SELECT *
FROM Sales.SalesOrderDetail d
WHERE d.CarrierTrackingNumber = N'E257-40A1-A3'
AND h.SalesOrderId = d.SalesOrderId
)
So which of these methods provides the best performance? How do you go about evaluating this? Note
first that each is using a highly selective WHERE predicate. The value E257-40A1-A3 occurs in only one
row in 121,317 in the Sales.SalesOrderDetail table. This high selectivity should produce a plan where
the first step can resolve to one row and will enable you to focus on the subsequent responses of the
optimizer to each query method. By executing all three SQL commands in a batch, you can get an idea of
the relative costs of the statements to each other. In this case these plans all resolve to the same cost. The
plan in Figure 9-17 for 34 percent is the result of a rounding to the full 100 percent.
Figure 9-17
This first execution plan corresponds to the JOIN and you can see that result of the Index Scan on
Sales.SalesOrderDetail results in Figure 9-18, by the one row that is used in a nested loop to seek into
the matching Sales.SalesOrderHeader table. The problem is that by joining these two result sets in a
nested loop the sort order can't be guaranteed, so the Sort step is added to the plan to produce the sorted
result.
In the subquery method, the rows are filtered at the Sales.SalesOrderDetail level before loading
one at a time into a Stream Aggregate operator. Because the subquery has to be resolved first before the