Database Reference
In-Depth Information
Comparing Cardinality Estimators: Joins
Let's look at how both models handle joins and create another table, as shown in Listing 3-18. The table has a single
ID column populated with data from the CETest table and references it with a foreign key constraint.
Listing 3-18. Cardinality estimators and joins: Creating another table
create table dbo.CETestRef
(
ID int not null,
constraint FK_CTTestRef_CTTest
foreign key(ID)
references dbo.CETest(ID)
);
-- 72,089 rows
insert into dbo.CETestRef(ID)
select ID from dbo.CETest;
create unique clustered index IDX_CETestRef_ID
on dbo.CETestRef(ID);
Note
We will discuss foreign key constraints in greater depth in Chapter 7, “Constraints.”
As a first step, let's run the query with a join, as shown in Listing 3-19. This query returns data from the CETestRef
table only. A foreign-key constraint guarantees that every row in the CETestRef table has a corresponding row in the
CETest table; therefore, SQL Server can eliminate the join from the execution plan.
Listing 3-19. Cardinality estimators and joins: Test query 1
select d.ID
from dbo.CETestRef d join dbo.CETest m on
d.ID = m.ID
Note
We will discuss join elimination in greater depth in Chapter 9, “Views.”
Figure 3-22 shows the cardinality estimations for the query. As you can see, both models work the same, correctly
estimating the number of rows.
Figure 3-22. Cardinality estimations with join elimination
 
 
Search WWH ::




Custom Search