Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Jul 9 '12 at 18:03
add comment

migrated from stackoverflow.com Jul 9 '12 at 17:56

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

2 Answers

up vote 3 down vote accepted

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?

http://msdn.microsoft.com/en-us/library/ms190787

share|improve this answer
1  
+1 - The real question here is why does OP want to partition –  JNK Jul 9 '12 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 Jul 9 '12 at 18:25
2  
@JNK And now the real question here is: "How can I fix my deadlock problem?" –  Cade Roux Jul 9 '12 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 Jul 9 '12 at 18:26
1  
Have you considered changing your isolation level? Lock escalation? Why do you have transactions locking an entire table? –  JNK Jul 9 '12 at 18:48
show 3 more comments

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

Country_id | Order_id

Country_id | Order_id | OrderLine_id

Country_id | Order_id | OrderLine_id | Ship_Id
share|improve this answer
    
This also really begs the question of why partition. If you need to have this much redundancy what is the projected benefit? –  JNK Jul 9 '12 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. –  AngryHacker Jul 9 '12 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 Jul 9 '12 at 18:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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