Sql-server – Switching a partition on a table with one to many relation

partitioningsql server

There is two main tables in database (SQL Server 2014)

table1:
----------------------------------------------
id         name         datetime        image

table2:
--------------------------------
id         Address      phone     person_id 

As you can guess there is an "one to many" relation between two tables, and the foreign key of [table2] is [person_id].

I'm trying to use partition table and sliding window features on table1. Everything perfectly done (I tested sliding window and partitioning on single table successfully) except one thing.

For sliding window , the oldest partition will be switch to an Auxiliary table (with same structure of table1) and the newest partition will split for new datas.

Partition function and scheme is created on datetime column.

As Microsoft mentioned,:

For switching task, the all indexes must be aligned.

In our situation the table1 has a primary key that is unique non-clustered index too (the database made it automatically) and the foreign key of table2 connected to that.

When stored procedure tried to switching out the oldest partition, it rises an error:

the table1 is partitioned but the [primary key index] column is not partitioned

So now the all indexes is not aligned because of this primary key !

I tried to include datetime to primary key, but database created an index for new primary key automatically on filegroup instead of partition scheme. Also tried other situations but no difference.

My final purpose is to switch last partition to auxiliary table and truncate that(about one billion records per partition). So the related records in table2 must delete too.

I'm looking for a solution to solve this situation and handle partitioning, switching and truncating on related tables with related columns (like this one).

Please don't attend so much to the structure or image storing stuffs! , I just tried to propose a concept from my circumstance.

Best Answer

You mentioned you added datetime to the PK, but it was not partitioned. You also need to specify the partition scheme when you recreate the PK index so that it is aligned.

Also, note there are restrictions on tables with foreign key relationships. See Transferring Data Efficiently by Using Partition Switching.

Unfortunately, SQL Server didn't ask anything about index creation on partition or filegroup. I performed that with visual features.

The GUI is nice for some simple quick tasks but not for partitioning a table with billions of rows. Use T-SQL so that you know exactly what is going on. Large tables are unforgiving.