I am working on a SQL Server 2016 SP2 Standard edition server, with an in-house application that is 24/7 used in multiple locations across multiple countries. The current application has one database per Customer (company using the application and central server). Eventually, there will be hundreds of Customers with databases in this system.

There is also one "Central" database, which includes all archival data from all of the individual DBs. A customer Id value is used to keep all of the different data separated.

To speed access to archival data, and to simplify maintenance, the current plan is to partition this Central DB, with the appropriate by-customer tables partitioned into one partition per customer, each on a separate physical file in a separate DB partition.

However, this DB and the relevant tables are already in use in an unpartitioned way. I am researching what needs to be done to split things out into the desired data structures.

Example: There is a table (Orders) that includes CustomerID. The desire is to split this existing table so there is one Customer per Filegroup/file based on the CustomeId. The partitioning function, partitioning scheme, and other miscellany is simple enough to figure out. However, the only examples I can find for splitting a table across filegroups involve creating a new table. The documentation doesn't explicitly state that ONLY a newly created table can do this in partitioning, and some older articles I have read (SQL 2005 and 2008) say that you can't do this.

So, the final question: Other than by creating a duplicate table with the correct partitioning, migrating the data, and renaming the old and new tables, is there any way to partition an existing table into multiple different new filegroups / physical files?

Directions to examples for SQL 2016 (or 2012 if it can be done there or later) will be accepted as well.


  • I did examine that post, but it does not address partitioning an existing DB into multiple physcal partitions. – Laughing Vergil Nov 8 '18 at 22:04
  • 1
    That is what a partition scheme does, which is the first bullet point on Kin's answer. – LowlyDBA Nov 8 '18 at 22:07
  • If you are moving the data to new filegroups, why do you think you could do this without actually moving the data? You have to migrate the data to get it onto the new filegroups. So I'm not sure it matters that you are creating a new table to do it... – Aaron Bertrand Nov 8 '18 at 22:53

Create partition functions and partition schemes on required file groups. Then just rebuild/create the clustered index of required table on related partition scheme. It will involve data movement and take time. You can also use ONLINE = ON for keeping the table available.

| improve this answer | |

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