What is difference between Clustered Index and Non-Cluster Index?

Clustered Index
Database index strategy
Non-Clustered Index
What is difference between cluster index and non cluster index?

Answer Wiki

Thanks. We'll let you know when a new response is added.

“There are clustered and nonclustered indexes. 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.”


Discuss This Question: 6  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    A clustered index doesn't actually change the order that the data is physically sorted in. It used to back in SQL 6.5, but it hasn't since then. I posted about the differences on my blog. When the clustered index is built the indexed columns are sorted within each page in the correct order just like a normal index, however the clustered index points back to the heap (the table) for the other values. Row level data within the table is sorted in the order it is written to the data page which is why the only way to guarantee order is to use the ORDER BY.
    69,000 pointsBadges:
  • Whateverelse
    You did not say which database you are using. I defer to Mr. Denny regarding SQL Server, but other databases do things different. In Informix, for example, you can run ALTER INDEX ... TO CLUSTER. This will cause the table to be reorganized immediately, with the rows stored in the order that matches the key of the index that was ALTERed. In DB2, a clustering index attempts to maintain clustering even as new rows are inserted. This can lead to page splits when the inserted row belongs on a page that is already full. These splits used to be called "near-off" or "far-off" splits (don't know if that terminology is still in use -- it's been a while), depending on whether there was an empty page (see FREEPAGE parameter) nearby that could be used to hold the data split from the original page. Additionally, when the table is REORGed, these splits will be consolidated and the rows will be placed in CLUSTER key order. I do not know if Oracle has anything comparable.
    25 pointsBadges:
  • carlosdl
    I think the closest thing in Oracle would be Index Organized Tables (IOT).
    84,745 pointsBadges:
  • Sivu
    Hi Amin, Clustered index sorts the records accordingly and stores in the table. Non-Clustered index is not physically ordered but ordered its offset. Regards, Siva
    105 pointsBadges:
  • TomLiotta
    HEY WIKE YOU UNIT INCH GOAT...  Yeah, we remember when we had our first beer, too.  -- Tom
    125,585 pointsBadges:
  • Gentledanny
    Thank you Denny Cherry.
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: