I thought that SQL Server maintained a "logical file", but the OS maintained the "physical file". If you start with a small mdf file that grows in small chunks on a heavily used disk, each time the file grows, it would grab a piece of disk that was available, but that new piece might not be physically contiguous with the last piece, and might actually be before any of the other parts of the file or intermixed with others (I'm thinking about disk fragmentation here). SQL Server would see one long continuous "logical file", but at the physical disk level, it would actually be spread all over the disk. Because of this, a clustered index would be logically ordered, but might not be physically ordered on the disk. I'm using the terms logical and physical file loosely here (not really sure what term is most appropriate to describe what I'm thinking).

If it didn't work this way, every time your mdf file grew, it might have to rebuild some of your clustered indexes to make sure they were physically ordered on disk, right?

I know my technical jargon might be a little off, but am I correct on the concept? I always thought that saying clustered indexes were "ordered on disk" was a good way to think about and understand the concept, but was not always completely technically true when you got down to the nuts-n-bolts.

Thanks,

Chad