Bhuvnesh (1/6/2013)
external fragmentation is basically handled by sql server internally
No, it's not. Fixing 'external fragmentation' requires an index rebuild or index reorganise.
and data get inserted in mixed extent initially (i think we can keep "table having identity col's PK" out here ) later on data moves into contigous extent. this could be reason why external fragmentation get avoided.
The external fragmentation he's talking about is not extent fragmentation. It's logical fragmentation, pages having their physical order not matching their logical order.
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