<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Gail Shaw
»
Introduction to Indexes
123 posts, Page 11 of 13
««
«
9
10
11
12
13
»
»»
Introduction to Indexes
Rate Topic
Display Mode
Topic Options
Author
Message
GilaMonster
GilaMonster
Posted Wednesday, January 26, 2011 1:51 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 37,648,
Visits: 29,900
JacekO (1/26/2011)
Wow,
Now instead of giving me a fish you are trying to send me out to sea with a net...
And instructions.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1054205
vj2173
vj2173
Posted Friday, January 28, 2011 8:53 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, April 06, 2011 9:00 AM
Points: 6,
Visits: 16
I'm an extreme Newbie and I'm trying to clarify my own understanding of indexes. You mentioned that,
"The non-leaf levels of the index contain one row per page of the level below, referencing the lowest index key value on each page."
Correct me if I'm wrong, but the root page contains the actual values from the index column.
ID
1
2
3
4
This can span out if the index column has 10,000 records?
ID
1
2
3
...
.. 10,000
those values are divided up within the Intermediate Level
ID (Intermediate1)
1
2
3
4
ID (Intermediate2)
5
6
7
8
this spread across to accomodate the number of rows in the index column
ID (IntermediateN?)
..
..
record 10,000
this is then populated in the Leaf level? Is this presumption, correct?
Thanks,
Post #1055372
GilaMonster
GilaMonster
Posted Friday, January 28, 2011 9:10 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 37,648,
Visits: 29,900
I'm not sure I understand you. I suggest you read parts 2 and 3 where I have diagrams of the index structure.
The only level of the index that has all key values is the leaf level.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1055390
hallidayd
hallidayd
Posted Friday, January 28, 2011 9:22 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, March 03, 2013 2:50 AM
Points: 112,
Visits: 294
Gail is right - the root and intermediate index levels store only key range boundary values since all they are required for are routing to the relevant next page. There is no reason to store every value - that would be inefficient.
Post #1055396
vj2173
vj2173
Posted Friday, January 28, 2011 9:27 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, April 06, 2011 9:00 AM
Points: 6,
Visits: 16
Thank you for the response. I read the 2nd article and I notice that in your diagram you have
1
4
13
for the Intermediate Level. And like your last response, the leaf level contains all of the data for the particular row.
1 Mr. Adam Taylor
2. Mr. Richard Wright
3. Miss Helen Smith
Which I follow. My confusion is with the root and intermediate pages.
From your diagram, am I to assume that the root page contain the indexed column and all of the values from the index column. From
1 to 137?
Secondly, the Intermediate pages (level) hold segments of those values from the indexed column?
Post #1055401
mtillman-921105
mtillman-921105
Posted Friday, January 28, 2011 9:44 AM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 10, 2013 11:54 AM
Points: 749,
Visits: 3,767
A phone book analogy is often used here. You have a list of people's names in a phone book. The root would be analogous to the first letter of their last names. The intermediate would be subgroups of that, so, for example, Smith to Styles (but not their individual values), and the leaf would be the individual entries (each person's name (and telephone number)).
(Changed my avatar from a Husky to Carl from the movie UP! for the new year.
)
______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1055414
vj2173
vj2173
Posted Friday, January 28, 2011 9:45 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, April 06, 2011 9:00 AM
Points: 6,
Visits: 16
Yes! Now it makes sense. I couldn't grasp the Intermediate Level and it's use.
Danke!
Post #1055417
GilaMonster
GilaMonster
Posted Friday, January 28, 2011 9:50 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 37,648,
Visits: 29,900
vj2173 (1/28/2011)
From your diagram, am I to assume that the root page contain the indexed column and all of the values from the index column. From
1 to 137?
No. Just 1 and 137. It doesn't need any values between those, the root only stores the starting value for each page beneath it
Secondly, the Intermediate pages (level) hold segments of those values from the indexed column?
Again, no. The intermediate pages in that diagram contain only the values specified. Like the root, the lowest key value for each page beneath it.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1055420
e-ghetto
e-ghetto
Posted Tuesday, July 03, 2012 4:34 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, April 22, 2013 2:31 AM
Points: 199,
Visits: 205
Clustered indexes define the
logical
order of the table
I would say: Clustered indexes define the
physical
order of the table. Am I right?
Post #1324281
AndrewJacksonZA
AndrewJacksonZA
Posted Tuesday, July 03, 2012 5:37 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 5:52 AM
Points: 165,
Visits: 1,022
e-ghetto (7/3/2012)
Clustered indexes define the
logical
order of the table
I would say: Clustered indexes define the
physical
order of the table. Am I right?
No. There is no guarantee of physical order whatsoever. It's the
logical
order.
Post #1324317
« Prev Topic
|
Next Topic »
123 posts, Page 11 of 13
««
«
9
10
11
12
13
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
