Post reply

Clusterd index storage

  • November 22, 2010 at 4:40 am

    #231795

    Hi All

    While going thru one of the articles from Gail, I found that

    "a clustered index has the data stored logically in the order of the index key".

    The link is given below:

    http://www.sqlservercentral.com/articles/Indexing/68563/

    I had the perception that clustered index physically reorders the data according to index key.So I check the link from microsoft to confirm on this:

    http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx

    It says:

    "Because the clustered index dictates the physical storage order of the data in the table"

    Althought the above link is for sql 2000, I belive it should hold good for 2005 as well.

    So i thought of taking some more openions on it. Your comments pls?

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • November 22, 2010 at 6:14 am

    #1253691

    MSDN is wrong (for 2000 as well as 2005).

    If the index has no fragmentation and the file it is in has no fragmentation then it may be that the logical and the physical (on disk) order of the data are the same. Generally however that is not the case.

    The clustered index enforces the logical order. The physical order is subject to a large number of other factors.

    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

  • November 22, 2010 at 9:54 pm

    #1254166

    Hi Gail

    I can't doubt your words. Thats why despite MSDN saying it, I posted for confirmation again. Thanks for confirming it.

    Are there any links which specifically address this topic and correctly too 🙂

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • November 23, 2010 at 1:02 am

    #1254217

    S_Kumar_S (11/22/2010)

    Are there any links which specifically address this topic and correctly too 🙂

    NOt exactly what you want but YES ..some help is here http://sqlinthewild.co.za/index.php/2008/10/20/what-is-fragmentation/

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply