Post reply

Filegroup for Indexes, what about the Clustered Index

  • February 17, 2010 at 3:04 am

    #220703

    As many of you know there is a recommendation to put the indexes into another filegroup then the data of an table.

    I was wondering if this recommendation is true for the clustered index as many people say it "IS" the table.

    It could properly mean that the clusterd index tells the table how to sort the rows the actual index is just the tree-structure?

  • February 17, 2010 at 3:42 am

    #1119935

    Most operation in SQL 2005 were placing non-clustered index on separate file group, on SQL 2008 this rule applies.

    Clustered index always resides with base table and cannot be placed on different file group than the base table.

    Cheer Satish 🙂

  • February 17, 2010 at 4:00 am

    #1119942

    Thanks for your info.

    But I must say, I tried to put the clusterd key index in another filegroup and it worked.

    I not saying that it makes sense but it worked :w00t:

  • February 17, 2010 at 4:49 am

    #1119959

    Yes, clustered index can be added on another filegroup. But why this is not recommended to have the clustered index on another filegroup.

    -Lk

  • February 17, 2010 at 5:09 am

    #1119965

    mitch.fh (2/17/2010)

    Thanks for your info.

    But I must say, I tried to put the clusterd key index in another filegroup and it worked.

    I not saying that it makes sense about it worked :w00t:

    When you create the clustered index on another filegroup, the table also move with it.

    This method is used during horizontal partitioning.

  • February 17, 2010 at 6:28 am

    #1120022

    mitch.fh (2/17/2010)

    I was wondering if this recommendation is true for the clustered index as many people say it "IS" the table.

    The leaf level of the clustered index contains the actual data rows of the table. If you move the clustered index to a different filegroup, then you have actually moved the table. Hence if your rules say indexes on a different filegroup from the table, you want the clustered index (which is the table) in one and the nonclustered indexes in an other

    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

  • February 17, 2010 at 6:41 am

    #1120035

    Thank you all. I will do it like said:

    clustered index -> data-filegroups

    nonclustered index -> index-filegroups

    Thanks for the quick replies 😉

Viewing 7 posts - 1 through 6 (of 6 total)

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