Moving Tables & Indexes between Filegroups
In my previous posts I discussed about Filegroups, we will see very brief summary of filegroup again in this post.
Filegroup - A filegroup is a logical structure to group objects in the database. We can create multiple filegroups in the database, the default filegroup is PRIMARY for any database. We can create filegroups for two main reasons 1) Performance and 2) Recovery
Performance - For suppose if a filegroup exists on D:\drive has very large table that is getting frequently accessed and you are receiving high IO, in that case we can move the large table to new drive where it has high IO handling capacity for better performance.
Recovery – Filegroups can be backed up and recovery individually, this can enable faster recovery in case of disaster. In disaster we can recover the primary filegroup first and make database accessible and then we can recover archived data filegroups which is not frequently used by users.
Sometimes we may need to move objects between filegroups to achieve performance or moving data to another drives or to archive the unused tables into different drive in a separate filegroup.
Select * from sys.filegroups -- Shows how many files groups are associated to the database
If there are more than one filegroup exists then we need to identify default database by seeing Is_default = 1 value.
Below query helps to identify the object name and associated file group to it.
SELECT 'Object Name' = OBJECT_NAME(IDX.object_id), 'Index Name' = IDX.name,
'FG Name' = FG.name FROM sys.indexes IDX INNER JOIN sys.filegroups FG ON IDX.data_space_id = FG.data_space_id
Now we will add new filegroup to database
Alter database KalyanDB add FileGroup SecondaryFG
Select * from sys.filegroups
ALTER DATABASE KalyanDB ADD FILE (NAME = 'Secondary_File',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\secondary.ndf') TO FILEGROUP SECONDARYFG
Below query displays file group and associated physical file to it
select fg.name as filegroup, (case when fg.is_read_only =0 then 'Write Mode' else 'Read Mode' end),
f.name, f.physical_name from sys.filegroups fg, sys.database_files f
where f.data_space_id = fg.data_space_id
We will create two tables, One in default file group and other in secondary file group
CREATE TABLE Table1_PrimaryFG( [ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL ) ON PRIMARY
CREATE TABLE Table2_SecondaryFG([ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL
) ON SECONDARYFG
Run the below query to identify the table1 and table2 in which file group
SELECT 'Object Name' = OBJECT_NAME(IDX.object_id), 'Index Name' = IDX.name,
'FG Name' = FG.name FROM sys.indexes IDX
INNER JOIN sys.filegroups FG ON IDX.data_space_id = FG.data_space_id
where OBJECT_NAME(idx.object_id) in ('Table2_SecondaryFG','Table1_PrimaryFG')
To achieve moving objects while creating clustered index on the table we can specify the filegroup so the table will automatically moved to the other filegroup.
Below create commands will move table to SecondaryFG file group
Create clustered index idx_table1 on Table1_PrimaryFG(ProductID) on SecondaryFG
Create clustered index idx_table2 on Table2_SecondaryFG(ProductID)
Below syntax is used to move table when already index is exists on the table.
CREATE CLUSTERED INDEX [idx_table1] ON [dbo].[Table1_PrimaryFG] ([ProductID] ASC )WITH (DROP_EXISTING = ON, ONLINE = ON) ON [PRIMARY]
CREATE UNIQUE INDEX / NONCLUSTERED INDEX ux_id ON [dbo].Table3_PrimaryFG ( id ) WITH (DROP_EXISTING = ON) ON [SecondaryFG]
Another query to identify objects with file group information.
SELECT object_name(i.[object_id]) as Name_of_Object, i.name as Index_Name,
i.type_desc as Index_Type, f.name as Name_of_Filegroup, a.type as Object_Type,
f.type, f.type_desc FROM sys.filegroups as f INNER JOIN sys.indexes as i
ON f.data_space_id = i.data_space_id INNER JOIN sys.all_objects as a
ON i.object_id = a.object_id WHERE a.type ='U'
-- User defined tables only
AND object_name(i.[object_id]) in ('Table2_SecondaryFG','Table1_PrimaryFG')
