|
|
Posted by Hugo Kornelis on 10/02/26 11:58
On Sun, 17 Sep 2006 18:34:59 GMT, Curt wrote:
>What is the difference please?
Hi Curt,
SQL Server has two types of indexes, clustered and non-clustered.
The similarity is that both are organised as a B-tree, with root and
intermediate pages holding indexed values and pointers to pages on the
next lower level.
On the lowest ("leaf") level, pages in a clustered index contain the
values of the indexed columns of each individual row in the table; in
addition, the values of all other columns are also stored in the same
leaf page. This means that all data in a table is stored in the leaf
pages of a clustered index - this is the reason why only one clustered
index per table is allowed, and why in simplified descriptions, the
table data is said to be "stored in clustered index order". This is only
true as long as you realise that the order is logical, achieved by
following pointer chains - in reality, the data will probably be
scattered all over the sectors of your hard disk, or even spread over
multiple spindles.
In a non-clustered index, the leaf pages also contain the values of the
indexed columns of each row - but in this case, these values are only
accompanied by a pointer to where the full row is stored. If the table
has a clustered index, this pointer is the clustered index key. If the
table has no clustered index (such a table is called a "heap"), this
pointer is a combination of file number, page number, and row number of
the page, pointing to the original location of the row. The row might
have moved; in that case a forwarding pointer in the original location
will point to the current location.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|