• When there IS a clustered index, the physical data is stored in the clustered index. So the leaf level of a CLUSTERED index = the actual record. Any non-clustered index points to the clustered index for its data, so given your description, in a clustered index the data is retrieved as soon as you access the leaf node, instead of the extra hop (called a "bookmark lookup") you end up with in a non-clustered index.

    Keep in mind that even the pages themselves are logical constructs. Granted - they're a fixed size, but they exist within physical files which grow as needed, so you could have multiple kinds of "physical fragementation". For example, with files that grow with "auto-growth on", the files themselves will be fragmented on the OS layer; the pages can physically exist anywhere within those OS files, then getting logically assigned to the index based on the usage and maintenance patterns on the table.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?