Recently one of my DBA friend had to transfer some huge tables from one Filegroup to another due to space restrictions and performance gains. He searched many blogs and forums and got confused as there are so many information available which are different from each other and hence sometimes can be misleading or confusing.
So he approached me and I helped him to finish the job and this incident gave the idea to write a blog about it as well for other DBAs and developers.
We can move the tables which have Clustered Indexes to another filegroups very easily. And if you want to move a table that does not have any Clustered Indexes then please create one on the table and then move ( ideally we should have Clustered Indexes on every table for performance gains).
Lets first check the properties of the table that has Clustered Indexes:
I am showing this blog with ‘[dbo].[Tbl_ContactOutcomeDetails_History]‘ table in one of my sample database.
As you can see that currently the table is on PRIMARY filegroup. Now we will Move this table to SECONDARY filegroup. We will use the Clustered Index called “IX_Tbl_ContactOutcomeDetails_History_CurrentTime” to move this table to SECONDARY filegroup.
This is the following syntax to move the table along with its Clustered Index to SECONDARY filegroup:
CREATE CLUSTERED INDEX [IX_Tbl_ContactOutcomeDetails_History_CurrentTime] ON [dbo].[Tbl_ContactOutcomeDetails_History]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Secondary]
After this query is successfully executed, let’s check the properties of the Table again:
So you can see that we have moved the table from PRIMARY filegroup to SECONDARY filegroup with single command which was not too difficult or confusing I assume 🙂 .