Difference Between Clustered and a Non-Clustered Index in SQL Server

Arun Choudhary

14y2k

Hi
What are the difference between clustered and a non-clustered index in SQL Server ?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Karthika Qpt10y ago
Clustered indexes don’t need to store a pointer to the actual row.
A clustered index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key.

Non clustered indexes store both a value and a pointer to the actual row that holds that value.
A non-clustered index requires separate storage than the table storage to store the index information.

Both are the terms used in sql.
0
Jignesh Trivedi14y ago
hi,

Nonclustered indexes have the same B-tree structure as clustered indexes, with two significant differences:
The data rows are not sorted and stored in order based on their nonclustered keys.
The leaf layer of a nonclustered index does not consist of the data pages.
Clustered indexes sort and store the data rows in the table based on their key values.

Please refer
http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005
http://www.sql-server-performance.com/forum/threads/cluster-and-non-cluster-index.6667/

hope this will help you.
0
Satyapriya Nayak14y ago
Hi Arun,

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.

A nonclustered 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 nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Thanks
0