Moving an existing table from one FileGroup to another FileGroup

We can move an existing Table from one filegroup to another filegroup using "MOVE TO" option.

To Identity the Default FileGroup
SELECT [Name] FROM sys.filegroups WHERE is_default =1


Creating a Table under default Filegroup
Create Table Tb_Table1
(
ID Int Identity,
Column1 Varchar(10)
)
Go


To Identity the table 'Tb_Table1' belongs to which Filegroup
SELECT d.[Name] 'File Group',
OBJECT_NAME(i.[Object_ID]) 'Table Name'
FROM sys.data_spaces d Join sys.indexes i On(d.data_space_id = i.data_space_id)
WHERE i.index_id <>'Tb_Table1') ORDER BY 2


Creating a new Filegrpup
Alter Database SQLServerBuddy Add FileGroup FG1
Go


Adding a File to the FileGroup
Alter Database SQLServerBuddy Add File
(
Name=FileGroup1,
FileName='C:\Filegroup1\FileGroup1.ndf',
Size=5MB,
FileGrowth=10%
) To Filegroup FG1
Go


To list out the FileGroups
SELECT [Name],is_default FROM sys.filegroups

Let's try to move the existing table Tb_Table1 from PRIMARY filegroup to FG1 filegroup
According to BOL Rules:
When a CONSTRAINT that created a CLUSTERED INDEX is deleted, the data rows that were stored in the LEAF LEVEL of the clustered index are stored in a NONCLUSTERED table(Heap). We can drop the CLUSTERED INDEX and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option. The MOVE TO option has the following restrictions:
1. MOVE TO is not valid for indexed views or nonclustered indexes.
2. The partition scheme or filegroup must already exist.
3. If MOVE TO is not specified, the table will be located in the same partition scheme or filegroup as was defined for the clustered index.


If the table doesn't have CLUSTERED PRIMARY KEY then, Create a CLUSTERED PRIMARY KEY.
Alter Table Tb_Table1 Add Constraint PK_Const_ID Primary Key(ID)
Go


To Move the table Tb_Table1 from PRIMARY filegroup to FG1 filegroup
Alter Table Tb_Table1 Drop Constraint PK_Const_ID WITH (MOVE TO FG1)
Go


To Move the table Tb_Table1 from FG1 filegroup to Default filegroup (if PRIMARY filegroup is a Default)
Alter Table Tb_Table1 Drop Constraint PK_Const_ID WITH (MOVE TO [Default])
Go

(or)
Alter Table Tb_Table1 Drop Constraint PK_Const_ID WITH (MOVE TO [Primary])
Go


Creating a Table in a specific Filegroup
Create Table Tb_Table2
(
ID Int Identity,
Column1 Varchar(10)
) On FG1
Go

Note: When you drop a clustered index, you can specify ONLINE = ON option so the DROP INDEX transaction does not block queries and modifications to the underlying data and associated nonclustered indexes.

No comments:

Post a Comment