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

id         name         datetime        image

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.

migrated from stackoverflow.com Apr 24 '15 at 7:06

This question came from our site for professional and enthusiast programmers.


table2, probably, will have more rows than table1, so partition for table2 is even more required than for table1. on the other side table2 does not have partition-key (datetime), so this "construction" is looks useless.

You need same partition key for both tables.

You may add datetime column to table2 and add it to PK - as result you will have duplicate data (same datetime stored in in both tables), extra column, and somehow sync updates (if datetime updateable).

Another way (I prefer this and did in my case) - review partitioning logic and set partition key as id for table1 and person_id for table2. PK for table2 will be person_id, id. Any other index also should include id or person_id correspondently. If you do so, then you will need manually or dynamicaly set ranges in partition function which match to your dates:

id     date      range
10001  1/1/2010  10001-12345
12346  1/1/2011  12346-45678
45679  1/1/2012  45679-...

Assume, that id and dates increments at the same time.

  • No the size of table2 is more less than table1 and there will be exist many records in table1 without any related record in table2 (with image), so I didn't planning for partition table2. But as I realized the table2 must be partitioned to. Is it true? – Hamed Hamedi Apr 24 '15 at 10:08
  • Absolutely no, you do not have to. In this case to make partition by date on table1 date should be added to PK (otherwise it will be useless). For table2, if you strongly want FK, then you should add date column or just remove FK. Forign Key is good tool, but as any tool it should not be used everywhere. – parfilko Apr 24 '15 at 18:42

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.