Statistics - Pro SQL Server Internals - page 53

EQ_ROWS indicates how many rows have a key value equal to the RANGE_HI_KEY
upper-bound value. In our case, there is only one row with ISBN = '104-0100002488' .
DISTINCT_RANGE_ROWS indicates how many distinct values of the keys are within the interval.
In our case, all of the values of the keys are unique and DISTINCT_RANGE_ROWS = RANGE_ROWS .
AVG_RANGE_ROWS indicates the average number of rows per distinct key value in the interval.
In our case, all of the values of the keys are unique and AVG_RANGE_ROWS = 1 .
Let's insert a set of the duplicate ISBN values into the index with the code shown in Listing 3-1.
Listing 3-1. Inserting duplicate ISBN values into the index
;with Prefix(Prefix)
as
(
select Num
from (values(104),(104),(104),(104),(104)) Num(Num)
)
,Postfix(Postfix)
as
(
select 100000001
union all
select Postfix + 1
from Postfix
where Postfix < 100002500
)
insert into dbo.Books(ISBN, Title)
select
CONVERT(char(3), Prefix) + '-0' + CONVERT(char(9),Postfix)
,'Title for ISBN' + CONVERT(char(3), Prefix) + '-0' + CONVERT(char(9),Postfix)
from Prefix cross join Postfix
option (maxrecursion 0);
-- Updating the statistics
update statistics dbo.Books IDX_Books_ISBN with fullscan;
Now, if you run the DBCC SHOW_STATISTICS ('dbo.Books',IDX_BOOKS_ISBN ) command again, you will see the
results shown in Figure 3-2 .