Example of simple Containment vs Base Containment

Question

The Legacy CE model assumes that users always query for data that exists. This means that, for a join predicate that involves an equijoin operation for two tables, the joined columns exist on both sides of the join. In the presence of additional non-join filter predicates against the join table, the Legacy CE assumes some level of correlation for the join predicates and non-join filter predicates. This implied correlation is called Simple Containment.

Alternatively, the New CE uses Base Containment as the correlation. The New CE model assumes that users might query for data that does not exist. This means that the filter predicates on separate tables may not be correlated with each other. Therefore, we use a probabilistic approach.

https://support.microsoft.com/en-us/help/3189675/join-containment-assumption-in-the-new-cardinality-estimator-degrades

what's the example of the above and really show statement is improved?

Answer 1

I seem to recall that you have asked about this before, and I seem to recall that I linked you to Joe Sack's white paper on the new cardinality estimator. Here it goes again: https://learn.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN

It was a while since I read it, but I would expect it to be covered there.