Sql-server – Can you create a new filegroup for a table in a database that is live

filegroupssql server

I have a SQL Server 12.0.5223.6 instance with a database on it.

I'm wondering if I can create a filegroup for one (or more) tables while it's running? Or does it have to have no data in the tables?

I could take the DB offline, but I can't take it offline for long. Right now all the data is inside Data.mdf. I'm hoping to split out some data from maybe three or four tables into separate filegroups.

Could this be done?

Best Answer

You can definitely add a filegroup to a database while it's running, but it will have no data until you create a table in it, or rebuild a table onto the new filegroup.

This would mean dropping the clustered key on the table, and re-creating it on the filegroup:

CREATE CLUSTERED INDEX IK_Example ON Example ( id ) ON NewFileGroup;

OR

ALTER TABLE Example ADD CONSTRAINT PK_Example PRIMARY KEY CLUSTERED (id) ON NewFileGroup;

The Microsoft Docs on this one is pretty good.

Related Question