Difference between Cluster and Non-cluster index?

Questions by bsush

Editorial / Best Answer

Answered by: Sushant

  • Oct 25th, 2006


The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

Showing Answers 1 - 1 of 1 Answers

Sushant

  • Oct 25th, 2006
 

The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

Rajesh Varma

  • Dec 7th, 2006
 

There can be only 1 Clustered index in a table

where as nonclustered index can be upto 249

Clustered is physical sorted index..

non clustered index is faster than clustered index

thats all i know

Regards

Rajesh Varma

TransWorks,Mumbai

  Was this answer useful?  Yes

I would say the way data is physically stored is decide via clustered index.

where as non cluster index pointer tables are created that points to data

Both are used to allows retrive data faster based on a particlar column.

There can be only on cluster index but can have multiple non cluster index.

  Was this answer useful?  Yes

hiseaman

  • Nov 20th, 2007
 

"non clustered index is faster than clustered index"
On the contrary, clustered index is faster than non-clustered index.
This is what clustered index is for.
(used in Sybase and MS SQL; MS SQL Server was orignally derived from Sybase.)

  Was this answer useful?  Yes

clustered index  - the index order matches the order of physically stored data.

cluster index can be used on table that doent get changes frequently

using DML statement on cluster index column have perfomance issues since it has to update the index each and every time a dml gets executed.

cluster advantageous in searching a range of values

Non-cluster - index order doesnt match with the physicall stored order

  Was this answer useful?  Yes

Sameer

  • Nov 1st, 2012
 

now SQL SERVER 2008 onwards we can have 1 cluster index and 999 non cluster index on one table.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions