Moving table to different filegroup using t-sql

does anyone know how to move a table (not just the indexes) to a different filegroup? I only find info on how to do this in EM. Even when I run a trace while doing it in EM I don't realy see anything in the trace that performs the action?
I'm trying to schedule this for the nighttime - hence the reason I want to do it through t-sql.
DBA72,
Do you have a clustered index on your table? If you do, you can move the
table to the new filegroup by issuing a create index statement WITH
DROP_EXISTING clause.
i.e. something like:
CREATE CLUSTERED INDEX mynewfilegroup ON mytable (col1, coln)
WITH DROP_EXISTING
ON filegroupname
Otherwise you will have to do what EM does. Create a temp table move your
data there, then create a new table on the new filegroup and copy it over.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"DBA72" <anonymous@.discussions.microsoft.com> wrote in message
news:AEF7FE8D-FB1A-4A58-81F5-149F65357BDB@.microsoft.com...
> does anyone know how to move a table (not just the indexes) to a different
filegroup? I only find info on how to do this in EM. Even when I run a trace
while doing it in EM I don't realy see anything in the trace that performs
the action?
> I'm trying to schedule this for the nighttime - hence the reason I want to
do it through t-sql.
|||And if the table does not have a clustered index, you simply add one,
and then drop it again. If you drop the clustered index, the data will
stay on the same filegroup. I.e. something like:
CREATE CLUSTERED INDEX indextodrop ON mytable (col1, coln) ON
filegroupname
DROP INDEX mytable.indextodrop
Hope this helps,
Gert-Jan
Mark Allison wrote:
> DBA72,
> Do you have a clustered index on your table? If you do, you can move the
> table to the new filegroup by issuing a create index statement WITH
> DROP_EXISTING clause.
> i.e. something like:
> CREATE CLUSTERED INDEX mynewfilegroup ON mytable (col1, coln)
> WITH DROP_EXISTING
> ON filegroupname
> Otherwise you will have to do what EM does. Create a temp table move your
> data there, then create a new table on the new filegroup and copy it over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "DBA72" <anonymous@.discussions.microsoft.com> wrote in message
> news:AEF7FE8D-FB1A-4A58-81F5-149F65357BDB@.microsoft.com...
> filegroup? I only find info on how to do this in EM. Even when I run a trace
> while doing it in EM I don't realy see anything in the trace that performs
> the action?
> do it through t-sql.
(Please reply only to the newsgroup)