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 only takes a minute to sign up.
Sign up to join this community
Sorry if this is a stupid question, but I'm setting up table partitioning for the first time. It will be on multiple existing tables all within the same database.
I know I need a partition function and a partition scheme, but do I need one of each for each table or a combination of 1 function and multiple partition schema or just one of each?
I'm using SQL Server 2014 Enterprise edition.
I tried Google but couldn't find the answer.
Hope someone can help.
Thanks
Alex
A separate partition function/scheme for each table/index provides the most flexibility and granularity but can increase complexity and redundant maintenance when boundaries are added or removed, such as when aligning or partitioning related tables.
I generally recommend a single function for all tables/indexes that are partitioned similarly and boundaries are always added/removed at the same time. You'll need separate partition schemes related to the function only if your file group mappings differ. You'll of course require separate partition functions if you need to maintain boundaries at different times.
A partition function gives the ranges to partition by, e.g. if you want to partition by years then your partitions could be 2012, 2013, 2014...
A partition scheme is a mapping of the "parts" of a given partition function to particular filegroups - if you are using the PRIMARY filegroup only, then they will all be mapped there, but some partitions could be on a different filegroup if you want.
A partitioned table has a partition scheme applied to a particular column. For example if your table is "Movies" then you might have a field "ReleaseYear" and all the movies released in 2012 would go into the 2012 partition, etc.
The partition scheme and function can be applied to more than one table, but every partitioned table must have one (by definition), they just don't need to be unique.
However, keep in mind that if you use the same partition scheme/function on more than one table, any changes you make to it will be applied to all the tables, e.g. splitting or merging a partition.
MSDN has some further information on partition schemes here.
Often you will find that you need to index those partitioned tables, and then you'd usually create a partitioned index as well. If it matches the partitioning scheme and field of the "base" table then it is known as apartition-aligned index, which allows special operations like "switching" data from one partition to another without having to physically move all the data.
There are no stupid questions. If you are using the same partition scheme and same partition function (e.g. put the same ranges to same file groups) then you can go by with the one partition scheme and one partition function. If not then you need multiple. Still you can use the same partition scheme with various partition functions if you put the ranges on the same file groups. HTH
