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

Tags:
Clustered Index
Database index strategy
Non-Clustered Index
What is difference between cluster index and non cluster index
ASKED: February 17, 2009  6:27 AM
UPDATED: February 20, 2009  7:02 AM

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.”

<a href=”http://www.mssqlcity.com/FAQ/General/clustered_vs_nonclustered_indexes.htm”>Source</a>.

Discuss This Question: 5  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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.
    65,450 pointsBadges:
    report
  • 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:
    report
  • carlosdl
    I think the closest thing in Oracle would be Index Organized Tables (IOT).
    65,110 pointsBadges:
    report
  • 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:
    report
  • TomLiotta
    HEY WIKE YOU UNIT INCH GOAT...  Yeah, we remember when we had our first beer, too.  -- Tom
    125,585 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following