|
Home > Archive > Microsoft SQL Server forum > April 2005 > How do ITables moved to a new FileGroup ?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How do ITables moved to a new FileGroup ?
|
|
| csomberg@dwr.com 2005-04-20, 3:23 am |
| 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 2005-04-20, 3:23 am |
| 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@dwr.com 2005-04-20, 9:23 am |
| Thanks !
| |
| johnbandettini@yahoo.co.uk 2005-04-20, 11:23 am |
| 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 !
| |
| johnbandettini@yahoo.co.uk 2005-04-20, 11:23 am |
| 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 !
|
|
|
|
|