Difference between Cluster and Non-cluster index?
Clustered index - the index order matches the order of physically stored data.
Cluster index can be used on table that doesn’t get changes frequently.
Using DML statements on cluster index column have performance 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 doesn’t match with the physical stored order.
There can be only 1 Clustered index in a table.
whereas non clustered index can be up to 249 .
Overall to say:
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.
No comments:
Post a Comment