• The Primary Key has to have the PartitionID column within it, as you've discovered.

    I don't understand why you need to drop any Foreign Keys before the Partition Switch, or are other tables referring to this data? If that is so, then shouldn't those tables be purged beforehand?

    Normally, you have as many partitions as you need plus one for any possible errors. So if you have a monthly scheme, there would be 12 - one for each month, plus another for any where the partition number calculation has got it wrong (an answer of 13, for example). In my example I didn't bother with the extra one because the calculation always produced 1, 2 or 3.

    What actually happens is your main table and partition switch table have exactly the same structure, with the same number of partitions. So the main table may have three partitions and the switch table has three partitions. If I want to switch partition 2, it will change from being 'pointed' (for wont of a better phrase) to the main table to being pointed to the switch table. At the same time the empty partition that was in the switch table also gets pointed to the main table. So two areas of storage are swapped. The switch table now has the data that was in the main table and the main table now has the empty space that was in the switch table. Then you can truncate the switch table and the data is removed.