Post reply

Partition switch on Parent table.

  • March 9, 2016 at 4:34 am

    #316709

    I've three tables A,B & C. B & C are referencing to table A on same column that is a uniqueidentifier . As per the requirement we need to choose Date column as partition key and obviously date field is not our primary key. Table A has got Unique key created on Uniqueidentifier type column and both B&C are referring to it .

    My requirement is to drop a partition from Parent table A so I've created an identical table A-temp and created FKs on table B & C referencing to A-temp table so that table A & A-temp will be identical then try to switch the partition to table A-temp but I got an issue like Unique key is not partitioned so the switch failed. As per the rule we've to make partition key is part of the Unique key (i.e uniqueidentifier + date key) on table A in order to create unique index on partitioned scheme. Adding date key to unique key will be a problem as we don't have date key on child tables and I cannot reference only Uniqueidentifier column on table A to create FKs on child tables.

    I hope you understand the issue and please let me know if you come across similar situation where you've to switch partition on parent table where partition key is NOT member of your unique/ primary key(non-clustered since partition key will be the clustered key).

  • March 9, 2016 at 2:17 pm

    #1864111

    If you want to do partition switching, then all indexes MUST be partitioned. There's no way around that. (Well, unless you are prepared to drop the index before the switch and recreate it after, but that probably defies the purpose of partition switching).

    So this means that every index must include the partitioning column, which also means that you probably cannot enforce the real uniqueness. I have had implementations where I had to add an EntryDate column to the primary key just so that I could partition by month, and accept that I now no longer can be sure that the "real" key is actually unique. It sucks, but I never found a way around this.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • March 11, 2016 at 8:47 am

    #1864605

    Thanks Hugo. It makes sense.

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

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