Tags

, , ,


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)
,NAME VARCHAR(50)
,Country VARCHAR(50)
) ON [primary]

— Inserting Dummy Data

INSERT INTO TAB1 VALUES (‘Sam’ ,‘UK’)
GO 1000

— Lets check the properties of the table

moveTable1

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.

moveTable2At this point, we have moved the table to the desired Filegroup. Now if needed, then we can keep the Clustered index or we can go ahead and drop the same.

DROP INDEX [idx_TAB1_id] ON [dbo].[TAB1] WITH ( ONLINE = OFF )
GO

Our table remains in the FG_Secondary and we have done our job.

Hopefully it helps.

Cheers,

Subhro Saha

 

Advertisements