Database Reference
In-Depth Information
insert into dbo.Cars(Make,Model)
select 'Honda', Model
from Models cross join IDs;
create statistics stat_Cars_Make on dbo.Cars(Make);
create statistics stat_Cars_Model on dbo.Cars(Model);
SQL Server correctly estimates cardinality when you run queries with a single predicate, as shown in Listing 4-17
and Figure
4-9
.
Listing 4-17.
Correlated predicates: Cardinality estimations with single predicates
select count(*) from dbo.Cars where Make = 'Toyota';
select count(*) from dbo.Cars where Model = 'Corolla';
Figure 4-9.
Cardinality estimations with a single predicate
However, cardinality estimations would be incorrect when both predicates are specified. Figure
4-10
illustrates
cardinality estimation for the query:
SELECT COUNT(*) FROM dbo.Cars WHERE Make = 'Toyota' and Model='Corolla'
when the legacy cardinality estimator is used.
