Index Question

[jonwolds]

Hi,

Is the following correct:

1) For a clustered index the last intermediate level in the b-tree points to the leaf level data page (but does not point to the exact row in the page).

2) For a non clustered index the leaf level in the b-tree points to the page and row in a data page.

Thanks
Jon

 

[hmckillop]

That is correct for 1 - In a clustered index, the data pages make up the leaf nodes.
2 -The leaf layer of a nonclustered index does not consist of the data pages.
Instead, the leaf nodes contain index rows. Each index row contains the nonclustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value


"I'm living so far beyond my income that we may almost be said to be living apart

 

[jonwolds]

I didn't explain myself too clearly. The main point of the post was to clarify that the Cluster Index has to search the data page for the required row, where as the rid has been pre-calculated in a non-clustered index.

This makes sense as the clustered index knows that the data in the page is ordered whereas the non clustered index will have to search a heap (assuming the table does not have a clustered index).

Your post answered my question - thanks
Jon