Partitioning and Foreign Keys

  • Pam Brisjar

    SSChampion

    Points: 12094

    See crappy pseudo-code below for the basic idea...

    PartitionTable is partitioned on a non-PK column. Yet we need the PK column to be the clustered key. Which means the PK must be part of the partitioning. BUT, we have tables that reference the PK as their FK. That doesn't work with having the partition column as part of the PK. Only options I can see are to drop the FK's (would not be a complete tragedy but not really a good idea) or not cluster on the PK, which would be really, really bad for performance. Partitioning on the PK (col_id) rather than col_status is NOT an option.

    Any other ideas anyone?

    Crappy pseudo-code:

    PartitionTable

    (Col_id int,

    Col_status int,

    Col_otherstuff varchar(25));

    ALTER TABLE PartitionTable ADD CONSTRAINT

    PK_PartitionTable PRIMARY KEY CLUSTERED

    (

    Col_ID

    ,Col_STATUS

    ) ON PART_SCH_STATUS(Col_STATUS)

    TableLink

    (link_id int,

    col_id int)

    ALTER TABLE TableLink WITH CHECK

    ADD CONSTRAINT Partition_LINK_FK FOREIGN KEY(Col_ID)

    REFERENCES [PartitionTable] (Col_ID)

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    Since your "PartitionTable is partitioned on a non-PK column" your PK could not be clustered. Easy as that.

    If in that scenario a non-clusterd PK proves to have a negative effect in performance that probably means partitioning strategy is not good.

    Ask yourself... why I am partitioning this table this way?

    ... does it helps during archive and purge processes?

    ... does it helps during querieng?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Pam Brisjar

    SSChampion

    Points: 12094

    All queries rely upon the two columns - sometimes both, sometimes just one of the two. The partitioning is needed and impacts performance. The clustering is needed and impacts performance. The partitioned key is not unique enough to be made the clustered key.

    What I have done for now is create a unique key on the id column. It makes the partitioning non-storage aligned but that's OK for now as sliding/archiving is not part of the plan.

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    :blink: now I'm confused.

    If partitioning strategy is not designed to help archiving/purging and at the same time is hurting performance... why partitioning?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Pam Brisjar

    SSChampion

    Points: 12094

    "impacts performance" = makes things run faster. So they are both good for performance.

  • vamshichiranjeevi.g

    Grasshopper

    Points: 20

    I have done some examples related to this topic and blog posted the same. Kindly go through them if needed

    https://www.chiranjeevivamsydba.com/2020/05/partitioning-with-primary-and-foreign.html

    https://www.chiranjeevivamsydba.com/2020/05/partitioning-with-primary-and-foreign_28.html

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

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