Clustered & Non-clustered Indexes

Posted on 25 Sep, 2022

Clustered

• Organises data "physically" on disk.

• Only 1 Clustered is possible. Since it "organises" data in 1-way. Every time you re-organise, you have a new clustered index.

• E.g Dictionary

• A primary key is usually the one which is used for clustered index. (It doesn't have to be)

• Makes queries faster as only the data/disk block is loaded which contains the clustered data. This reducing disk I/O

Non-clustered

• Points to data directly.

• Possible to have a lot of non-clustered indexes. Since its just a "data-structure" to speed up the process of looking something up.

• E.g Appendix at the back of book.

• Better for queries like SELECT * FROM TABLE WHERE name='bhupesh' assuming we have a non-clustered index on column name and you have a bunch of rows with name as 'bhupesh'.