Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««910111213»»»

Introduction to Indexes Expand / Collapse
Author
Message
Posted Wednesday, January 26, 2011 1:51 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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
Posted Friday, January 28, 2011 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 6, 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
Posted Friday, January 28, 2011 9:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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
Posted Friday, January 28, 2011 9:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:23 AM
Points: 112, Visits: 297
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
Posted Friday, January 28, 2011 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 6, 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
Posted Friday, January 28, 2011 9:44 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:08 AM
Points: 598, Visits: 3,816
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
Posted Friday, January 28, 2011 9:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 6, 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
Posted Friday, January 28, 2011 9:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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
Posted Tuesday, July 3, 2012 4:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:04 AM
Points: 202, Visits: 231
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
Posted Tuesday, July 3, 2012 5:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:35 AM
Points: 166, Visits: 1,056
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 »

Add to briefcase «««910111213»»»

Permissions Expand / Collapse