Post reply

Partitioning a table and its clustered index differently

  • May 27, 2010 at 1:34 am

    #141250

    Hi,

    I've got a table, lets call it Orders for simplicity. It has a PK on OrderID and a unique clustered index on OrderDate. I can't make OrderID part of the clustered index. This may look wrong because in real life it is a bit more complicated and I simplified the situation.

    What will happen if I partition the table by OrderID and partition the clustered index by OrderDate? Or partition the table by OrderID and not partition the clustered index?

    Thanks.

  • May 27, 2010 at 2:26 am

    #1173166

    The clustered index is the table, it's not a separate structure from the table.

    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

  • May 27, 2010 at 7:08 pm

    #1173670

    So, I guess, in case it is unique, you can only partition the table by one of the columns present in the clustered index... 🙁

  • May 28, 2010 at 9:47 am

    #1174028

    When partitioning a clustered index, the clustering key must contain the partitioning column. When partitioning a nonunique clustered index, and the partitioning column is not explicitly specified in the clustering key, SQL Server adds the partitioning column by default to the list of clustered index keys. If the clustered index is unique, you must explicitly specify that the clustered index key contain the partitioning column.

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

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