This is follow up question to @martin smith excellent answer https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean
After that i have watched MCM video of Paul randal which shows why index fragmentation matters more in large scans only for huge tables..If i understood correctly, external/logical fragmentation occurs when logical continuity of pages differ from physical order in which they should be..
Suppose i have big table and identity as clustered key and no updates to clustered key and all pages are in logical order,does Sql guarantee all these pages lay in disk in close proximity…during first inserts it may block some space for this table,but after that many inserts may happen to other tables as well,so data might not be in close proximity…Is my understanding correct ?
Best Answer
That may work for your clustered indexes, but you'll also have nonclustered indexes to contend with. They can suffer a lot too, and that's often where it can matter the most.
But you should consider the impact. There are times when fragmentation can be a big thing (such as when you delete data from heaps a lot), and times when it's really not such a huge problem. There are typically other matters that are more important to tune than trying desperately to avoid fragmentation.