Calculating leaf pages

I am trying to understand an example of indexing I read about.
Given the following SQL statement:
select title
from titles
where price between $20.00 and $30.00
The size of the table in rows and pages, the number of rows per page, and th
e
number of rows that the query returns:
1,000,000 rows (books)
190,000 are priced between $20 and $30
10 rows per page; pages 75 percent full; approximately 140,000 pages
There is a statement that I read which said:
Based upon a nonclustered index on price, title (and the table does not have
a clustered index) the query can perform a matching index scan, finding the
first page with a price of $20 via index pointers, and then scanning forward
on the leaf level until it finds a price more than $30. This index requires
about 35,700 leaf pages, so to scan the matching leaf pages requires about 6
,
800 reads.
The part I do not understand is "This index requires about 35,700 leaf pages
.
" I have a basic understanding of the leaf structure of a nonclustered index
,
but I do not understand the value 35,700. How was 35,700 determined or
calculated based off the given data?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1> The part I do not understand is "This index requires about 35,700 leaf
> pages.
> " I have a basic understanding of the leaf structure of a nonclustered
> index,
> but I do not understand the value 35,700. How was 35,700 determined or
> calculated based off the given data?
The number of leaf nodes in the non-clustered index cannot be calculated
from the data given. Storage requirements depend on the data types of the
key columns and, for variable length data, the average value length. It
seems the average title is about 50 characters, assuming a varchar data type
and smallmoney for price.
It appears to me the point of this indexing example is to demonstrate the
relative efficiency of a covering non-clustered index rather than how to
calculate space index requirements.
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards" <u3288@.uwe> wrote in message news:612e0f3c23791@.uwe...
>I am trying to understand an example of indexing I read about.
> Given the following SQL statement:
> select title
> from titles
> where price between $20.00 and $30.00
> The size of the table in rows and pages, the number of rows per page, and
> the
> number of rows that the query returns:
> 1,000,000 rows (books)
> 190,000 are priced between $20 and $30
> 10 rows per page; pages 75 percent full; approximately 140,000 pages
> There is a statement that I read which said:
> Based upon a nonclustered index on price, title (and the table does not
> have
> a clustered index) the query can perform a matching index scan, finding
> the
> first page with a price of $20 via index pointers, and then scanning
> forward
> on the leaf level until it finds a price more than $30. This index
> requires
> about 35,700 leaf pages, so to scan the matching leaf pages requires about
> 6,
> 800 reads.
> The part I do not understand is "This index requires about 35,700 leaf
> pages.
> " I have a basic understanding of the leaf structure of a nonclustered
> index,
> but I do not understand the value 35,700. How was 35,700 determined or
> calculated based off the given data?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1