Earlier, I wrote a blog about how we can move tables (with Clustered Indexes) to different filegroup. You can read the blog here. Today I am going to show how we can move table to different Filegroups that does not have any Clustered Index.
For Test purpose, I have created a dummy table called TAB1 on PRIMARY Filegroup and have inserted 1000 rows. Please note that this table does not have any Clustered index.
— Creating the Tables
CREATE TABLE TAB1 (
id INT identity(1, 1)
) ON [primary]
— Inserting Dummy Data
INSERT INTO TAB1 VALUES (‘Sam’ ,‘UK’)
— Lets check the properties of the table
Now, we have another Filegroup called “FG_Secondary” and we want to move table “TAB1” from Primary to FG_Secondary filegroup.
To do this, we need to create a clustered index on this table ( since it does not have any) on FG_Secondary Filegroup.
CREATE CLUSTERED INDEX idx_TAB1_id ON TAB1 (id)
WITH (ONLINE = ON) ON [FG_SECONDARY]
Note that we have created the Clustered Index on [FG_Secondary] filegroup. Now, lets check the property of the table.
DROP INDEX [idx_TAB1_id] ON [dbo].[TAB1] WITH ( ONLINE = OFF )
Our table remains in the FG_Secondary and we have done our job.
Hopefully it helps.