I have a table that has millions of records and we are looking at implementing table partitioning. Looking at it we have a foreign key "GroupID" that we would like to partition on. Is this possible?
The Group will have more entries added to it, so as new GroupID's are added can the partition's be made dynamically?
Yes you can use partitioning, yes new partitions can be made dynamically. It's easiest to use a partitioning scheme that does not need to be updated though.
SQL Enterprise 2005 & up:
SQL 2005 and up has built in transparent support for partitioning if you are using SQL development edition or enterprise edition. Dynamically adding more partitions means that you'd have to create a new partitioning function, new partition schemes and probably new file groups.
You can alter a partition function by using SPLIT:
ALTER PARTITION FUNCTION MyPartitionFunction ()
SPLIT RANGE (2);
SQL 2000 or SQL 2005 & up non-enterprise:
Otherwise you can still use partitioning but you need to do it the old MS SQL 2000 way. In the old way you have to physically create many tables, and then a view that is a UNION ALL of each of the tables. So you need knowledge of the schema before hand.
The good news about the old partitioning way is it's even easier to create new partitions dynamically. You just need to create a new table with a new constraint and update your view.
There are some limitations though, your partitioning column must be part of your primary key. And to do inserts directly into your View you'll need to NOT have an Identity column (one that is auto incremented by MS SQL)
Group ID may not be the best item to partition on, partitioning suits a rolling window effect and is designed more for removing of outdated data and the rolling in of new data.
By Grouping on ID you only allow yourself to add / remove groups, e.g. no element of time / longevity of data. There is also a hard limit of 1000 partitions per parrtitioned table, limiting you to 1000 groups. Since you intend on add group ID's and it is not indicated as a static number you will potentially hit this hard limit.
The only option to extend that limit of 1000 is to place a partitioned view across multiple partitioned tables.
The question I would ask is what causes new data to be added / older data to be removed and on what basis is that made. Just because the table is millions of rows does not make it an immediate candidate for partitioning, which is why I would ask the question.
In terms of dynamically adding them - that feature does not exist you would have to write the stored procedures and logic to handle the allocation of new filegroups / set next used and splitting of the partition function. It can be done, but SQL is not going to do it for you.