How to move a table from primary file group to another user defined file group?

Problem: How to move a table from primary file group to another user defined file group?

Solution: Create Clustered Indexes

When a table is created on default configured database all the tables data are stored on primary file group. In some situations, if you don’t specify any file group on a create statement of the table the default data file SQL Server chooses is PRIMARY file group to save the data on to the disk.

If the primary file group has too many tables there can be heavy IO consumption. thus, there will be an impact on performance of the queries on the server as well as the applications slow down. In that case, we can move the table to a different file group by shifting the clustered index of the table to a custom file group. If the table doesn’t have a clustered index you can create one and drop to serve the purpose for a while.

Example:

In this example, I am going to show we can create a special file group and shift the file group of a table that has been created on the primary.

Step 0: create a filegroup and assign name for it.

DECLARE      @DataPath NVarchar(max),

@db NVarchar(max),

@statement NVarchar(max),

@filename NVARCHAR(256)

--change the database name as needed.

SET @db='Db'

--change the filename varibale as needed.

SET @filename='Test_12123'

--change the datapath as needed

SET @DataPath='C:\TEmp'

IF (@DataPath IS NULL  or @DataPath='')

BEGIN

SET @DataPath= (select SUBSTRING(physical_name,1, charindex('\master.mdf',physical_name, 1)-1) from sys.master_files where name = 'master')

END

IF NOT EXISTS (select * from sys.filegroups where name=@filename)

BEGIN

                SET @statement = N'ALTER DATABASE ' + @db + ' ADD FILEGROUP  '+@filename

                EXECUTE dbo.sp_executesql @statement

                SET @statement = N'ALTER DATABASE ' + @db + '             ADD FILE (NAME = ' + @db+'_'+@filename+', FILENAME = ''' + @DataPath+ '\'+@db+'_UD'')

                TO FILEGROUP '+@filename

                EXECUTE dbo.sp_executesql @statement

END

Step 1: Pick the table to move from the primary file group or a file group from where you want to transfer the table.

SELECT o.[name] AS TableName, i.[name] AS IndexName, f.[name] as FileGroup

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = f.data_space_id

AND o.[name] = 'History'

AND i.name is not null

OUTPUT:

TableName	IndexName	FileGroup
History	ClusteredIndex-20170110-211852	PRIMARY

Step 2:

Move the clustered Index to a custom file group in my case I have a file group called User_data

CREATE CLUSTERED INDEX [ClusteredIndex-20170110-211852] ON DB.dbo.History(UnitId)

WITH(DROP_EXISTING=ON,Online=ON) ON [USER_DATA]

GO

Step 3:

Verify the data location of the table after moving to see everything is set as expected. Run the same query as in the step 1(above)

TableName	IndexName	FileGroup
History	ClusteredIndex-20170110-211852	USER_DATA