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



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?
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