Nonclustered Index Structure - Help! 
Author Message
 Nonclustered Index Structure - Help!

Hi,

I need somebody's help to clarify my understanding about nonclustered index
structure. My understandings are:

If a table has a CLUSTERED index,
1. The bookmark on the leaf level of the NONCLUSTERED INDEX is the clustered
index key, but on the root level or intermediate levels, the bookmark is
still a RID, in the form like File#:Page#:Slot#. Am I right?

2. The bookmark on the root level or intermediate levels of a CLUSTERED
INDEX is a RID,  in the form like File#:Page#:Slot#. Am I right?

3. SQL Server automatically keeps the clustered key in all nonclustered
indexes, along with the corresponding nonclustered key. My understanding is,
this is only on the leaf level of the nonclustered indexes. On root level or
intermediate levels of a nonclustered index, there is no clustered key. Am I
right?

Thanks in advance.

Richard



Sat, 22 May 2004 02:43:06 GMT
 Nonclustered Index Structure - Help!

Richard,

For 1 & 2 I would think of it this way.  For any levels above the Leaf level
both Clustered (CI) and Non-Clustered (NC) indexes will just point to the
next level down in the tree (not to the actual data) until they get to the
Leaf Level.  For a NC index on a table with a CI the leaf level it will have
a bookmark lookup to the CI (which is the row of data itself).  For a NC
with no associated CI the leaf level will have a pointer to the row on the
data page.   The CI leaf level is already the data so there is no lookup
necessary.  As for # 3 that is how I see it since there would be no benefit
to having the CI info other than at the leaf level of the NC index.

--
Andrew J. Kelly,  SQL Server MVP
TargitInteractive

Quote:

> Hi,

> I need somebody's help to clarify my understanding about nonclustered
index
> structure. My understandings are:

> If a table has a CLUSTERED index,
> 1. The bookmark on the leaf level of the NONCLUSTERED INDEX is the
clustered
> index key, but on the root level or intermediate levels, the bookmark is
> still a RID, in the form like File#:Page#:Slot#. Am I right?

> 2. The bookmark on the root level or intermediate levels of a CLUSTERED
> INDEX is a RID,  in the form like File#:Page#:Slot#. Am I right?

> 3. SQL Server automatically keeps the clustered key in all nonclustered
> indexes, along with the corresponding nonclustered key. My understanding
is,
> this is only on the leaf level of the nonclustered indexes. On root level
or
> intermediate levels of a nonclustered index, there is no clustered key. Am
I
> right?

> Thanks in advance.

> Richard



Sat, 22 May 2004 03:23:13 GMT
 Nonclustered Index Structure - Help!
Thanks and have a nice day!

Richard



Quote:
> Richard,

> For 1 & 2 I would think of it this way.  For any levels above the Leaf
level
> both Clustered (CI) and Non-Clustered (NC) indexes will just point to the
> next level down in the tree (not to the actual data) until they get to the
> Leaf Level.  For a NC index on a table with a CI the leaf level it will
have
> a bookmark lookup to the CI (which is the row of data itself).  For a NC
> with no associated CI the leaf level will have a pointer to the row on the
> data page.   The CI leaf level is already the data so there is no lookup
> necessary.  As for # 3 that is how I see it since there would be no
benefit
> to having the CI info other than at the leaf level of the NC index.

> --
> Andrew J. Kelly,  SQL Server MVP
> TargitInteractive


> > Hi,

> > I need somebody's help to clarify my understanding about nonclustered
> index
> > structure. My understandings are:

> > If a table has a CLUSTERED index,
> > 1. The bookmark on the leaf level of the NONCLUSTERED INDEX is the
> clustered
> > index key, but on the root level or intermediate levels, the bookmark is
> > still a RID, in the form like File#:Page#:Slot#. Am I right?

> > 2. The bookmark on the root level or intermediate levels of a CLUSTERED
> > INDEX is a RID,  in the form like File#:Page#:Slot#. Am I right?

> > 3. SQL Server automatically keeps the clustered key in all nonclustered
> > indexes, along with the corresponding nonclustered key. My understanding
> is,
> > this is only on the leaf level of the nonclustered indexes. On root
level
> or
> > intermediate levels of a nonclustered index, there is no clustered key.
Am
> I
> > right?

> > Thanks in advance.

> > Richard



Sat, 22 May 2004 03:33:40 GMT
 Nonclustered Index Structure - Help!
Much informative, Andrew!

Dinesh.


Quote:
> Richard,

> For 1 & 2 I would think of it this way.  For any levels above the Leaf
level
> both Clustered (CI) and Non-Clustered (NC) indexes will just point to the
> next level down in the tree (not to the actual data) until they get to the
> Leaf Level.  For a NC index on a table with a CI the leaf level it will
have
> a bookmark lookup to the CI (which is the row of data itself).  For a NC
> with no associated CI the leaf level will have a pointer to the row on the
> data page.   The CI leaf level is already the data so there is no lookup
> necessary.  As for # 3 that is how I see it since there would be no
benefit
> to having the CI info other than at the leaf level of the NC index.

> --
> Andrew J. Kelly,  SQL Server MVP
> TargitInteractive


> > Hi,

> > I need somebody's help to clarify my understanding about nonclustered
> index
> > structure. My understandings are:

> > If a table has a CLUSTERED index,
> > 1. The bookmark on the leaf level of the NONCLUSTERED INDEX is the
> clustered
> > index key, but on the root level or intermediate levels, the bookmark is
> > still a RID, in the form like File#:Page#:Slot#. Am I right?

> > 2. The bookmark on the root level or intermediate levels of a CLUSTERED
> > INDEX is a RID,  in the form like File#:Page#:Slot#. Am I right?

> > 3. SQL Server automatically keeps the clustered key in all nonclustered
> > indexes, along with the corresponding nonclustered key. My understanding
> is,
> > this is only on the leaf level of the nonclustered indexes. On root
level
> or
> > intermediate levels of a nonclustered index, there is no clustered key.
Am
> I
> > right?

> > Thanks in advance.

> > Richard



Sat, 22 May 2004 03:41:43 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. HELP! need details on nonclustered index

2. Clustered index vs Nonclustered index

3. Nonclustered indexes suffer with clustered index?

4. 6.5 to 8.0 nonclustered index perf problems

5. nonclustered and clustered indexes

6. clustered + nonclustered indexes

7. nonclustered index

8. Clustered/Nonclustered Indexes

9. Nonclustered indexes getting corrupted

10. Space Required to rebuild nonclustered index

11. Confusion about Nonclustered indexes

12. Covering index (nonclustered and clustered)


 
Powered by phpBB® Forum Software