Changing file group of a table

Question

Changing file group of a table

Jason-407360

Old Hand

Points: 387
More actions
October 1, 2008 at 11:22 pm

#127825

I have to change the file group of a table which is like 26GB.I am trying to do it using the statement
CREATE UNIQUE CLUSTERED
INDEX [PK_Tbl_Order] ON [Tbl_Order]([EmpID])
WITH
DROP_EXISTING
ON [SECONDARY]
What will be status of the database if it fails.I am running the statement through SQL jobs and during non production hours.
Please give me some input ASAP.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

michaela SSCertifiable Points: 5649 More actions · Answer 1

As much as I can see, you're trying to create a clustered index on a separate filegroup than the one where your table resides on. By definition, all clustered indexes are created on the same file group where the table is stored; you can have non-clustered indexes built for the same table that can be stored on a different filegroup.

However, for moving a table to a separate filegroup, you would need first to grab your contraint definitions, then run

ALTER TABLE )

then re-create the contraint using definitions at first step.

michaela SSCertifiable Points: 5649 More actions · Answer 2

it was not meant to insert a wink; the statement is

alter table );

Jason-407360 Old Hand Points: 387 More actions · Answer 3

My intention is to change the file group of the table.

Presently it is in primary file group and i want to move it to secondary.

We can do it in 2 ways:

1.Through enterprize manager ,go to design tab and change the filegroup.

2.Through scripts you can do it by recreating the primary key of the table on the secondary file group dropping the existing one.

Now i wanted to know what if the script fails.What will be the status of the database and the index on the table.