Statistics - Pro SQL Server Internals - page 72

Figure 3-15. Up-to-date statistics: Cardinality estimations for value, which is not a key in the histogram step
Finally, let's run a parameterized query, as shown in Listing 3-15, using a local variable as the predicate.
In this case, SQL Server uses average selectivity in the index and estimates the number of rows by multiplying the
density of the key by the total number of rows in the index: 0.002739726 * 65536 = 179.551 . Both models produce
the same result, as shown in Figure 3-16 .
Listing 3-15. Up-to-date statistics: Selecting data for unknown value
declare
@D date = '2013-06-25'
-- New Cardinality Estimator
select ID, ADate, Placeholder
from dbo.CETest with (index=IDX_CETest_ADate)
where ADate = @D
-- Legacy Cardinality Estimator
select ID, ADate, Placeholder
from dbo.CETest with (index=IDX_CETest_ADate)
where ADate = @D
option (que2rytraceon 9481);
Figure 3-16. Up-to-date statistics: Cardinality estimations for an unknown value
As you can see, when the statistics are up to date, both models provide the same results.