In what order does a clustered index store data?

Hi

I was going through the book by Kalen Delaney where she has mentioned the following paragpraph in Chapter 7 (Index Internals):

Many documents describing SQL Server indexes will tell you that the clustered index physically stores the data in sorted order. This can be misleading if you think of physical storage as the disk itself. If a clustered index had to keep the data on the actual disk in a particular order, it could be prohibitively expensive to make changes. If a page got too full and had to be split in two, all the data on all the succeeding pages would have to be moved down. Sorted order in a clustered index simply means that the data page chain is logically in order.

Then I read the book on SQL Server 2000 (on Perf Tuning) by Ken England. He says the clustered index stores data in physical order and any insert means moving the data physically. Also the same statement is echoed on the net by many articles.

What is the truth? How are really clustered index stored? What does physical order in the above statement really mean?

Regards

SanjaySi

It is an 'Apples and Oranges' difference.

Kalen is referrring to the actual physical disk sectors, where Ken is referring to the pages where SQL Server stores indexes.

The disk is NOT kept in perfect sequential order. Files can become fragmented from daily ordinary use. For indexes, SQL Server keeps a 'chain' of logical order, so that even where there is a page split, the chain is unbroken.

The 'Logical' order of clustered indexes is indeed kept in sequetial order. (Simplistic view coming...)

The index may be located on pages 1, 2, 3, 4, 5, 6, 27, 55, etc. The logical order 'chain', after page splits may be 1, 2, 55, 3, 27, 4, 5, 6, etc. And due to file fragmentation, the file may be stored in disk sectors 1,2, 454, 455, 1036, 1037, etc.