Move a table to other filegroup

What is the SQL command to move a table to another filegroup?

Do you use alter table for this?

Thanks,EB

Create the cluster on the file where you want to move the table.

Create clustered index idxname on TableName(colname) WITH DROP_EXISTING on NewFilegropName

http://msdn2.microsoft.com/en-us/library/ms175905.aspx


MohammedU.
Moderator
SQL-Server-Performance.com

Ok, but what if the clustered index is the primary key?

Thanks,EB

I don't think there will any issue...
You can test it out by creating small temporary permanant table in your db with PK as clustered index...


MohammedU.
Moderator
SQL-Server-Performance.com

When I try to change filegroup for a clustered primary key on a table without dependencies through SSMS i get



TITLE: Microsoft SQL Server Management Studio
------------------------------

Recreate failed for Index 'PK_PKTest'. (Microsoft.SqlServer.Smo)

For help, click:http://go.microsoft.com/fwlink?Prod...ceptionText&EvtID=Recreate Index&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Table 'PKTest' already has a primary key defined on it.
Could not create constraint. See previous errors. (Microsoft SQL Server, Error: 1779)

For help, click:http://go.microsoft.com/fwlink?Prod...47&EvtSrc=MSSQLServer&EvtID=1779&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------



The help link displayed in the error message wasn't any help at all.

I suspect that it is not possible to change a primary key but you have to drop it and recreate it on desired filegroup.

The only way I found to move a table to a different filegroup is this :

1. drop any foreign keys pointing to the primary key
2. drop the clustered primary key with ALTER TABLE [tablename] DROP CONSTRAINT [name of primary key]
3. create the clustered primary key on different filegroup with ALTER TABLE [tablename] ADD CONSTRAINT [name of primary key] PRIMARY KEY CLUSTERED ([Id]) ON [filegroup]
4. create the foreign keys dropped in 1.

This is when the primary key is clustered which is true in most cases.

Anyone experiencing the same?


Thanks,EB

Yes you are right...

From BOL...
You can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup.

If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. However, if the index definition is altered the statement fails. To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.

http://msdn2.microsoft.com/en-us/library/ms176118.aspx




MohammedU.
Moderator
SQL-Server-Performance.com

Hi... can please someone tell me if there is a way to move data from a partitioned table to a table in a different filegroup.
My table is on a daily partition and we regularly want to move data from the table to a different filegroup.
Is this possible in sql server 2005. if yes, please let me know the steps that needs to be done.
suba