Menu
Possible Duplicates:
Difference between clustered and nonclustered index
What do Clustered and Non clustered index actually mean?
Hi experts,
What does the word "clustered" mean in "clustered index"? I am doubting that it has something to do with the disk sector usage. Because I vaguely remember that Windows organizes disk space into clusters, which is composed of one or more 512-byte sectors. Do these 2 concepts have any connections?
Thanks.
366
Yes, you can create a clustered index on key columns that contain duplicate values.
Clustered means that records with similar keys are stored (for the most part) next to each other on disk. So if you have a key with just 1 integer column, the record with a value of "1" will be located next to the record with value "2".
Unique Non Cluster Index only accepts unique values. It does not accept duplicate values. After creating a unique Non Cluster Index, we cannot insert duplicate values in the table.
A clustered index is an index which defines the physical order in which table records are stored in a database. Since there can be only one way in which records are physically stored in a database table, there can be only one clustered index per table. By default a clustered index is created on a primary key column.
A clustered index represents the physical order of the records on disk. Nonclustered indices are merely "pointers" to the physical records in the table; they are in order of their key(s) and contain the data of their keys and any included columns.
Consider the index of a book vs. its page numbers: the index contains an alphabetized list of topics, and maybe it contains a summary of the topic, but the topics themselves are on the referenced pages. Page numbers, then, would be the clustered index.
It follows that you should consider choosing an immutable, monotonically increasing primary key for the clustered index so that things don't need to be rearranged when inserting and updating.
81
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
