Clustered and non Clustered Index

Hi Vikas,


Clustered indexes cause the data to be ordered according to the index,
which means reordering happens every time the index changes, for whatever
reason. Could be an UPDATE, or an INSERT.
Since clustering affects the physical order of data, a table can have a
maximum of 1 clustered index.

The general rule of thumb is to:
1: cluster on columns where you fetch ranges
2: cluster on columns you frequently order by
3: cluster on primary key. Of dubious value in my opinion.

One main and importtant difference is that when a table doesn't hava a clustered index,
any non-clustered index contain a pointer to the actual data in the
table. When a clustered index is created, all non-clustered indexes are
rebuilt to contain the index columns of the clustered index, which may
lead to slower access.

Quite often, it's more valuable to consider creating covering indexes,
i.e. an index containing all columns required to run a query, thus
eliminating looking in the actual data pages.

About reclaiming unused space: Yes, rebuilding a non-clustered index
doesn't affect actual table space, but a clustered index *includes* the
data pages, hence the change in space usage when rebuilding the index.


Thanks.

Hi Vikas,

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

Clustered index is physically stored a table can have 1 clustered index

 

A non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-Clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Non clustered index is logically stored a table can have 249 non Clustered index



Thanks