moving tables

Hi,

ms-sqlserver 2000 sp4 - entrerprise edition

Can you help me on this issue. I would like to move some user
tables(about 100 MB) to new file group. Can you tell me what I need to
do?

Thanks in advance,

If you have clustered index on all the tables then just recreate the
clustered and non-clustered indexes and specify the new filegroup. Else
you can bcp out the data and then create the table in new file group and bcp
in the data.

"he_msql" wrote:

> Hi,
>
> ms-sqlserver 2000 sp4 - entrerprise edition
>
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
>
> Thanks in advance,
>
>

Hi,

Due to the details of moving tables (i.e. moving constraints, etc), it is
considered best practice to allow Enterprise Manager do the work for you.
Enterprise Manager knows what to move.  I haven't found a stored procedure t
o
do the trick, so you will have deal with the GUI of Enterprise Manager.  If
you happen to know one, please post.

This subject is located in the Books Online under  'filegroups:switchin
g
filegroup for table'.   Here is the procedure...

How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
1. Expand a server group, and then expand a server.
2. Expand Databases, expand the database in which the table belongs, and
then click Tables.
3. In the details pane, right-click the table, and then click Design Table.
4. Right-click any column, and then click Properties.
5. On the Tables tab, in the Table Filegroup list, select the filegroup on
which to place the table.
6. Optionally, in the Text Filegroup list, select a filegroup on which to
place any text, image, and ntext columns.

HTH,
Adam

"he_msql" wrote:

> Hi,
>
> ms-sqlserver 2000 sp4 - entrerprise edition
>
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
>
> Thanks in advance,
>
>

If the tables are defined relations (i.e. Foreign Key constraints), then
it is probably easiest to use Enterprise Manager to do the work and/or
create the script to do the work.

If there are no tables referencing the table you want to move, and if
this table has a clustered index, then the most efficient way to move
it, is to recreate the clustered index with the original name and
definition with "CREATE INDEX ... ON ...(...) WITH DROP_EXISTING ON
[your target filegroup]"

The same method can be used for nonclustered indexes.

If the table is a heap, then create a clustered index ON [your target
filegroup] and drop the clustered index afterwards.

HTH,
Gert-Jan

he_msql  wrote:
>
> Hi,
>
> ms-sqlserver 2000 sp4 - entrerprise edition
>
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
>
> Thanks in advance,