469,133 Members | 1,179 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

Indexes vs Clustered Indexes

What is the difference please?
Sep 17 '06 #1
1 1693
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
Sep 17 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Chris | last post: by
4 posts views Thread by eXavier | last post: by
2 posts views Thread by David Sharp | last post: by
1 post views Thread by Steve_CA | last post: by
9 posts views Thread by Igor | last post: by
7 posts views Thread by robertbrown1971 | last post: by
5 posts views Thread by shelleybobelly | last post: by
2 posts views Thread by Lyle Fairfield | last post: by
reply views Thread by Utahduck | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.