Statistics - Pro SQL Server Internals - page 77

Let's change our query and add a column from the referenced table to the result set. The code for doing this is
shown in Listing 3-20.
Listing 3-20. Cardinality estimators and joins: Test query 2
select d.ID, m.ID
from dbo.CETestRef d join dbo.CETest m on
d.ID = m.ID
Even though a foreign-key constraint guarantees that the number of rows in the result set will match the number
of rows in the CETestRef table, the legacy cardinality estimator does not take it into consideration, and therefore
underestimates the number of rows. The new cardinality estimator does a better job, providing the correct result.
Figure 3-23 illustrates the latter.
Figure 3-23. Cardinality estimations with join
It is worth mentioning that the new model does not always provide a 100 percent correct estimation when joins
are involved. Nevertheless, the results are generally better than with the legacy model.
Comparing Cardinality Estimators: Multiple Predicates
The new cardinality estimation model removes the Independence assumption, and it expects some level of correlation
between entities attributes. It performs estimations differently when queries have multiple predicates that involve
multiple columns in the table. Listing 3-21 shows an example of such a query. Figure 3-24 shows the cardinality
estimations for both models.
Listing 3-21. Query with multiple predicates
-- New Cardinality Estimator
select ID, ADate
from dbo.CETest
where
ID between 20000 and 30000 and
ADate between '2014-01-01' and '2014-02-01';
-- Legacy Cardinality Estimator
select ID, ADate
from dbo.CETest
where
ID between 20000 and 30000 and
ADate between '2014-01-01' and '2014-02-01';
option (querytraceon 9481)