Does Cluster index really change the physical order of record?

Question

In most of the book we read that cluster index is the physical order and noncluster index is logical order of data. I have a table with more than 80 lac rows with cluster index, How this will impact on row insertion. means if cluster index is physical order of data then new data insertion must be very slow and time must be increase on next insertion, but its not happening, why ?

Answer 1

That depends on the kind of data you are putting in the index. If it's an auto-increment, then new records will tend to cluster together at the end, and insertions will be efficient. But if it's a GUID, then every insertion will touch a random page and performance will plummet.

Answer 2

No. Inserting into the middle of a clustered index does not mean that the physical position of all subsequent records needs to be adjusted!

If there is insufficient free space on the page for the record then page splits will occur and the logical order will become different than the physical order. This is a type of fragmentation that can be dealt with by periodic reorganisation or rebuilding of the index.