top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Difference between clustered and a non-clustered index?

+1 vote
336 views
Difference between clustered and a non-clustered index?
posted Aug 24, 2014 by Deepak Negi

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote

One table can only have only one clustered index.
One table can only have many non-clustered index.
A clustered index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key.
A non-clustered index requires separate storage than the table storage to store the index information.
A table with a clustered index is called clustered table. Its rows are stored in a B-Tree structure sorted.
A table without any clustered indexes is called non-clustered table. Its rows are stored in heap structure unsorted.
The default index created as part of the primary key column is a clustered index.

answer Nov 17, 2014 by Manikandan J
0 votes

Here’s a summary of the differences:

  • A clustered index determines the order in which the rows of the table will be stored on disk – and it actually stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored.
  • Using a clustered index is an advantage when groups of data that can be clustered are frequently accessed by some queries. This speeds up retrieval because the data lives close to each other on disk. Also, if data is accessed in the same order as the clustered index, the retrieval will be much faster because the physical data stored on disk is sorted in the same order as the index.
  • A clustered index can be a disadvantage because any time a change is made to a value of an indexed column, the subsequent possibility of re-sorting rows to maintain order is a definite performance hit.
  • A table can have multiple non-clustered indexes. But, a table can have only one clustered index.
  • Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index – and the clustered index actually stores the row-level data in it’s leaf nodes.
answer Aug 26, 2014 by Vrije Mani Upadhyay
...