Does creating an clustered index physically sort the table

  • Hi,

    Does creating a clustered index on a table physically sorts the table or it sorts only the index pages.

    If it physically sorts the table then heavy IO should be generated and may slowdown the server and how sql server handles this.

    Thanks

    newbie

  • The clustered index is the table. A clustered index has, at the leaf level, the actual data pages of the table and SQL will do it's best, at time of index creation, to have those leaf pages stored in the file in order of the clustering key.

    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
  • I get my head around clustered vs. non-clustered indexes by thinking of a clustered index as being like a phone book.

    In a phone book data is sorted in the order of a particular field (last name) and because of this when you flick through a phone book and you find the name you want, you actually have the phone number \ address right there, no further looking up is required, this is because the index actually is the ordered data itself.

    For a non-clustred index I think of a conventional index in the back of a reference book.

    If you look up "Stored Procedure" in the index at the back of one of the tomes on SQL Server, then you would see all the page numbers which contain a mention of this subject, 9, 27, 128-165 etc. But this isn't the data itself, merely a pointer to it.

    You have to then go to these page numbers to access the leaf-level data and read about Stored Procedures.

  • Andy Hogg (8/27/2009)


    I get my head around clustered vs. non-clustered indexes by thinking of a clustered index as being like a phone book.

    In a phone book data is sorted in the order of a particular field (last name) and because of this when you flick through a phone book and you find the name you want, you actually have the phone number \ address right there, no further looking up is required, this is because the index actually is the ordered data itself.

    For a non-clustred index I think of a conventional index in the back of a reference book.

    If you look up "Stored Procedure" in the index at the back of one of the tomes on SQL Server, then you would see all the page numbers which contain a mention of this subject, 9, 27, 128-165 etc. But this isn't the data itself, merely a pointer to it.

    You have to then go to these page numbers to access the leaf-level data and read about Stored Procedures.

    Good analogy , i like it 🙂



    Clear Sky SQL
    My Blog[/url]

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

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