469,600 Members | 2,211 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,600 developers. It's quick & easy.

How do ITables moved to a new FileGroup ?

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

Jul 23 '05 #1
4 1663
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

Jul 23 '05 #2
Thanks !

Jul 23 '05 #3
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
csomb...@dwr.com wrote:
Thanks !


Jul 23 '05 #4
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
csomb...@dwr.com wrote:
Thanks !


Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by el emperador | last post: by
reply views Thread by Louis | last post: by
14 posts views Thread by LineVoltageHalogen | last post: by
2 posts views Thread by si.downes | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.