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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.
This was first published in May 2001
Join the conversationComment
Share
Comments
Results
Contribute to the conversation