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

Question

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

Karthika Qpt · Answer

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.

Jignesh Trivedi · Answer

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.

Satyapriya Nayak · Answer

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