So I am just learning about clustered/nonclustered indexes. Now I read that clustered indexes order the data physically by i.e. the primary key.

But why would this even be necessary? Isn't the table ordered by the ID (Primary Key) by default? Because you start with record A (ID 1) then record B (ID 2) and so on. They are always sorted. Why is there a need for clustered indexes?

CodePudding user response：

Tables are not sorted. While an auto incremented ID is issued in ascending order, the DBMS is free to store the record wherever there is place on the disk. And if you query table data without an ORDER BY clause, you may get the rows in any old order.

An index on the ID can be used to find these rows quickly. It is very fast to find an ID in the index and the index tells you which row to read from the table.

If your table is all about finding a row by ID quickly, which is typical for mere lookup tables, say a table with all country names, you can instead make this a clusterted index.

"Clustered index" simply means that the whole table data is inside the index structure, so instead of searching the index and then get to the table row, you get the row straight away. Oracle has come up with a better name for this in my opinion; they call this "index organized table".