Database Reference
In-Depth Information
When you filter data based on both attributes, Query Optimizer correctly assumes that only a subset of the rows
will be red in color. Moreover, only some of the red articles will have a size equal to three. As a result, it expects that the
total number of rows with both predicates applied will be lower than with either of the single predicates applied.
While this approach works fine in most cases, it would introduce incorrect cardinality estimation in the case
of highly correlated predicates. Let's look at another example and create a table that stores information about cars,
including their make and model. Listing 4-16 creates this table and populates it with some data. As a final step, it
creates column-level statistics on both columns.
Listing 4-16.
Correlated predicates: Table creation
create table dbo.Cars
(
ID int not null identity(1,1),
Make varchar(32) not null,
Model varchar(32) not null
);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 CROSS JOIN N3 as T2) -- 256 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N4)
,Models(Model)
as
(
select Models.Model
from (
values('Yaris'),('Corolla'),('Matrix'),('Camry')
,('Avalon'),('Sienna'),('Tacoma'),('Tundra')
,('RAV4'),('Venza'),('Highlander'),('FJ Cruiser')
,('4Runner'),('Sequoia'),('Land Cruiser'),('Prius')
) Models(Model)
)
insert into dbo.Cars(Make,Model)
select 'Toyota', Model
from Models cross join IDs;
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 CROSS JOIN N3 as T2) -- 256 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N4)
,Models(Model)
as
(
select Models.Model
from (
values('Accord'),('Civic'),('CR-V'),('Crosstour')
,('CR-Z'),('FCX Clarity'),('Fit'),('Insight')
,('Odyssey'),('Pilot'),('Ridgeline')
) Models(Model)
)
