Imagine the following hypothetical database structure. Basically it's a top down structure (Country has Orders, Order has OrderLines, OrderLine has ShipLines, etc...).

enter image description here

I want to partition my SQL Server 2008 database by CountryID. As you can see, CountryID is only in the first 2 tables (e.g. amCountries and amOrders). Given that (as far as I understand) that partitioning is done at the table level, do I need to add CountryID to the remaining tables to be able to partition them? Or is there some type of cascade available in SQL Server that will allow me to skip adding CountryID to everything?

  • 1
    Well bear in mind you partition TABLES not databases. You may want to reconsider partitioning - what do you think the benefit will be for your scenario?
    – JNK
    Commented Jul 9, 2012 at 18:03

2 Answers 2


Partitioning is only at the table level and is for managing partitions of that table. Typically the advantage of partitioning is in swapping in and out data and for getting additional control of the granularity of storage and backup. To some extent it can also help with performance if there are shared partition columns in a join, but that probably shouldn't be a reason to partition.

I understand your thinking about the child tables, but if you want that data also partitioned by country (for some of the above reasons), you would have to add a column because the partition function is restricted.

What is your thinking about why you want to partition in the first place?


  • 1
    +1 - The real question here is why does OP want to partition
    – JNK
    Commented Jul 9, 2012 at 18:12
  • 1
    @AngryHacker The most common cause of deadlock are operations done in different orders. I'm not optimistic that partitioning will really help with that. If your operations are not in a consistent order or your locks are escalating to the point that you end up with table locks which end up causing non-obvious deadlocks, I don't really see partitioning helping. The best way to avoid deadlocks is to have good normalization and consistent order of operations (especially in small transactions versus the occasional batch operations).
    – Cade Roux
    Commented Jul 9, 2012 at 18:25
  • 2
    @JNK And now the real question here is: "How can I fix my deadlock problem?"
    – Cade Roux
    Commented Jul 9, 2012 at 18:26
  • 1
    I agree with Cade, this is a bad reason to partition. You really need to understand the source of the deadlocks first before you redesign your entire database to prevent them.
    – JNK
    Commented Jul 9, 2012 at 18:26
  • 1
    Have you considered changing your isolation level? Lock escalation? Why do you have transactions locking an entire table?
    – JNK
    Commented Jul 9, 2012 at 18:48

Typically Partitioning is on Date ranges. Having said that, your idea of migrating the Country column to the child tables seems unavoidable. In that case, ditch the surrogate keys you have and use the composite keys, like this:


Country_id | Order_id

Country_id | Order_id | OrderLine_id

Country_id | Order_id | OrderLine_id | Ship_Id
  • This also really begs the question of why partition. If you need to have this much redundancy what is the projected benefit?
    – JNK
    Commented Jul 9, 2012 at 18:15
  • @JNK The purpose of partitioning is that the database suffers too many deadlocks due to concurrent transactions going against these tables from different angles/indexes. Since logically all the queries are separated by CountryID anyway, partitioning, should in theory eliminate the deadlocks, since there wouldn't be any more contention. Commented Jul 9, 2012 at 18:27
  • @AngryHacker See my comment on Cade's answer. You have no way to know that until you know what's causing the DLs. If my car makes a loud noise, I can replace the tires since the tires meet the road and the road is where the noise seems to come from, but that doesn't mean it's not an axle problem or something else.
    – JNK
    Commented Jul 9, 2012 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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