How do ITables moved to a new FileGroup ?

[csomberg]

I have a SQL Server 2000 database and want to now split up the tables
onto seperate file groups as well as some indices.

How do you breakup an existing table to move it from one filegroup
(Primary) to the new filegroup ?

Thanks.

Craig

[Simon Hayes]

Create (or recreate) a clustered index on the table, and specify that
it should be created on the new filegroup. Since a clustered index
contains the table data at its leaf level, moving the clustered index
moves the table.

Simon

[csomberg]

Thanks !

[johnbandettini]

A word of warning, although creating or rebuilding a clustered index,
moves a table to a new filegroup it does not necessarily move all
database objects. I moved a lot of tables from one filegroup to
another. I was a bit surprised sometime latter when one of my
colleagues told me there were a lot of tables on the wrong filegroup.

On investigation I found the script he ran did not differentiate
between tables or other objects. There were no tables there but there
were things like non-clustered non-unique indexes, primary keys etc.

You can use this script to check what objects are where.

select g.name,object_name(i.id),i.indid,i.groupid from sysindexes i
join sysfiles g on i.groupid = g.groupid
order by g.name,object_name(i.id),i.indid

Hope this helps

John
[e-mail] wrote:

Thanks !

[johnbandettini]

A word of warning, although creating or rebuilding a clustered index,
moves a table to a new filegroup it does not necessarily move all
database objects. I moved a lot of tables from one filegroup to
another. I was a bit surprised sometime latter when one of my
colleagues told me there were a lot of tables on the wrong filegroup.

On investigation I found the script he ran did not differentiate
between tables or other objects. There were no tables there but there
were things like non-clustered non-unique indexes, primary keys etc.

You can use this script to check what objects are where.

select g.name,object_name(i.id),i.indid,i.groupid from sysindexes i
join sysfiles g on i.groupid = g.groupid
order by g.name,object_name(i.id),i.indid

Hope this helps

John
[e-mail] wrote:

Thanks !