• Bhuvnesh (10/28/2013)


    I also read it many places but does microsoft(msdn) say the same ?

    Books Online states that the clustered index enforces physical storage order. Books Online is wrong, on this and a number of other issues. It's easy to prove too.

    Index fragmentation is defined (in BoL) as the % by which the physical order of the index differs from the logical order.

    Books Online states that a clustered index enforces physical storage order of the index (to match logical order)

    Therefore (based on the prior two premises), a clustered index always has 0% logical fragmentation.

    Now we know that to be false, clustered indexes can and do have non-zero fragmentation, so following the rules of logic one of the premises must be false. The definition of logical fragmentation is correct (and can be proven), so it must be the physical storage order premise that is wrong.

    Following logically from that, one has to conclude that clustered indexes do not enforce physical storage order and any time the logical fragmentation is non-zero the logical and physical order of the clustered index doesn't match, hence the rows are not physically stored in clustered index order.

    Q.E.D.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass