SQL Server :: Moving Table to secondary data file or Different Filegroups

You may have already seen many posts on same topics as i experienced but i never got all of my answers in single post hence i thought to write this post.

Question: Is it online or offline operation?

Answer: It's online operation with few consideration. Also it's strongly recommended to do this during non-business hours if you are doing it in production and that too for huge tables.

You can follow below mentioned steps to move a table to a particular file group. A FILE-GROUP may contain one or more data files. If the FILE-GROUP contains more than one data file, SQL Server uses a proportional fill algorithm to make use of all data files in the FILE-GROUP. On the other hand, if the FILE-GROUP contains a single data file, then you can say that you have moved the table to a particular file.

To move a table to a particular file-group, you need to re-create the clustered index on the table. Recreating an index effectively moves the table to the new FILE-GROUP.


Database Name:[AdventureWorks2012]
Table Name: [Person].[Person]

Important Note:

You can not add or remove a file while a BACKUP statement is running.

Create New File-Group:

USE Master;
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP FG_Secondary
GO

Add file to Newly created File-Group:

ALTER DATABASE [AdventureWorks2012]
ADD FILE
(
NAME = FG_Secondary,
FILENAME = 'F:\mpdbs001\sqlappdb_V4RC11\AdventureWorks2012_Data2.ndf',
SIZE = 500MB,
MAXSIZE = 1000MB,
FILEGROWTH = 50MB
) TO FILEGROUP FG_Secondary;
GO

Select the table name and press Alt+ F1 and verify the data located on file-group column.
Currently it is on PRIMARY file-group.

Run Below mentioned script to move table named [Person].[Person] to filegroup named FG_Secondary.

CREATE Unique CLUSTERED INDEX [PK_Person_BusinessEntityID]
 ON [Person].[Person]
 (
  [BusinessEntityID] ASC
  )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON)
  ON FG_Secondary
GO

-----------------You may experience below mentioned error ----------

Error: Could not drop the primary key constraint 'PK_Person_BusinessEntityID' because the table has an XML or spatial index.
Solution : Script out and Drop the XML Index First and then drop the Primary Key Constraint

-----------------------------------------------------------------------

You can see in below screen nip that table is now reside in secondary file group.

Hope it will help you.

Warm Regards,

Chhavinath Mishra 

Sr. Specialist Database Administrator