Database Reference
In-Depth Information
Table 7-2.
Characteristics of the Three Join Types
Join Type
Index on Joining Columns
Usual Size of Joining Tables
Presorted
Join Clause
Hash
Inner table: Not indexed
Outer table: Optional
Optimal condition: Small outer
table, large inner table
Any
No
Equi-join
Merge
Both tables: Must
Optimal condition: Clustered or
covering index on both
Large
Yes
Equi-join
Nested loop
Inner table: Must
Outer table: Preferable
Small
Optional
All
■
Note
the outer table is usually the smaller of the two joining tables in the hash and loop joins.
I will cover index types, including clustered and covering indexes, in Chapter 8.
Actual vs. Estimated Execution Plans
There are estimated and actual execution plans. To a degree, these are interchangeable. But, the actual plan carries
with it information from the execution of the query, specifically the row counts affected and some other information,
that is not available in the estimated plans. This information can be extremely useful, especially when trying to
understand statistic estimations. For that reason, actual execution plans are preferred when tuning queries.
Unfortunately, you won't always be able to access them. You may not be able to execute a query, say in a
production environment. You may have access only to the plan from cache, which contains no runtime information.
So, there are situations where the estimated plan is what you will have to work with.
However, there are other situations where the estimated plans will not work at all. Consider the following
stored procedure:
IF (SELECT OBJECT_ID('p1')
) IS NOT NULL
DROP PROC p1
GO
CREATE PROC p1
AS
CREATE TABLE t1 (c1 INT);
INSERT INTO t1
SELECT ProductID
FROM Production.Product;
SELECT *
FROM t1;
DROP TABLE t1;
GO
