Database Reference
In-Depth Information
create unique clustered index IDX_CETest_ID
on dbo.CETest(ID);
create nonclustered index IDX_CETest_ADate
on dbo.CETest(ADate);
If you examined nonclustered index statistics with the
DBCC SHOW_STATISTICS('dbo.CETest',IDX_CETest_ADate)
command, you would see results similar to those shown in Figure
3-13
. Actual histogram values may be different
when you run the script because the
ADate
values were generated randomly. Ignore the highlights in the figure for
now, though I will refer to them later.
Figure 3-13.
IDX_CETest_AData statistics
As you can see, the table has 65,536 rows. Let's test both cardinality estimators in cases where we use a predicate
for the value, which is a key in one of the histogram's steps. The queries are shown in Listing 3-13.
