Ask the Expert

Difference between clustered and non-clustered indexes

What's the difference between clustered and non-clustered indexes? When do you use them for assigning UNIQUE constraints?

    Requires Free Membership to View

Every index implicitly involves a logical sequence. This allows for searching. If the database engine is searching an index linearly, i.e. in logical sequence, it will know when to stop when it either finds the entry or notices that it has "gone past" the point in the sequence where the entry "belongs." (However, most database engines use more efficient index searches than linear.)

A clustered index is one in which the index entries are actually data records. So when the database searches for an entry, it will end up with the actual data a lot faster. This means that the index/data entries have to be physically stored on the disk in a manner that aids retrieval -- in most cases, this is in sequential order.

Contact the DBA for your installation to find out whether your database supports clustering indexes and whether he or she recommends them, and under what conditions.

Unique constraints should be declared separately from the indexes (it is only a coincidence that most database engines utilize an index in order to enforce a unique constraint). Again, consult your DBA about whether your particular database allows unique clustering indexes.

For More Information

This was first published in May 2001

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

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
Sort by: OldestNewest

Forgot Password?

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

Your password has been sent to: