Menu
How are clustered indexes stored on a hard disk? What is the logical order?
How do non-clustered indexes work?
238
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.
Clustered index is as same as dictionary where the data is arranged by alphabetical order. In clustered index, index contains pointer to block but not direct data. Example of Clustered Index – If you apply primary key to any column, then automatically it will become clustered index.
The clustered index defines the order in which the table data will be sorted and stored. As mentioned before, a table without indexes will be stored in an unordered structure. When you define a clustered index on a column, it will sort data based on that column values and store it.
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
In the Database, there is only one clustered index per table. A clustered index defines the order in which data is stored in the table which can be sorted in only one way. So, there can be an only a single clustered index for every table.
Your table can only be stored in one physical order. But certain times you need to look up data in other ways. For these scenarios, you use a nonclustered index. This is implemented as a B-Tree as well, but it doesn't have any bearing on the order of your table's data, like a clustered index does.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
This means that the data in the table are stored in a B-Tree according to the order of the CLUSTERED PRIMARY KEY (or the clustering columns).
This name is in my opinion a little bit confusing. The same concept in Oracle is called index-organized table which I find much more descriptive.
Non-clustered indexes contain the value of the indexed columns along with the pointer to the record they are originated from.
The "clustered index" is the table itself; the "non-clustered" index is an ordered copy of some of the table's columns.
If you "create" a clustered index, the table is rearranged. That's why you cannot have more than one "clustered index" on a table: the table cannot be arranged in more than one order.
If you create a secondary index, the shadow copy of the table is created, holding the values of the indexed columns and the pointers to the records they are from. Whenever the table changes, the copy is changed too (the engine takes care of that automatically).
id col1 value
-- -- --
1 1 Data 1
6 1 Data 6
3 1 Data 3
7 2 Data 7
9 2 Data 9
5 2 Data 5
The table is not ordered.
id col1 value
-- -- --
1 1 Data 1
3 1 Data 3
5 2 Data 5
6 1 Data 6
7 2 Data 7
9 2 Data 9
The table is ordered on id.
Table Index
id col1 value col1 id
-- -- -- -- --
1 1 Data 1 1 1
3 1 Data 3 1 3
5 2 Data 5 1 6
6 1 Data 6 2 5
7 2 Data 7 2 7
9 2 Data 9 2 9
The table is orderer on id, the index is ordered on (col1, id)
97
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
