Sorry guys. I wasn't very clear. I already have a script to move all index (except primary keys) to a different file group. However, with Primary Keys, the rules change. I can't just use the DROP_EXISTING option. It doesn't work. At lease not in 2005 Enterprise. FK constraints and such make this difficult.

Specifically, what I am trying to figure out is what is best practice about moving the Clustered or Non-clustered index on a table, that is also the primary key constraint to another file group?

So if I have the following table

create table tbl1

(id int primary key clustered,

col1 int,

col2 int,

col3 int

)

create nonclustered index idx1 on tbl1 (col1)

create nonclustered index idx2 on tbl1 (col3,col2)

Moving IDX1 and IDX2 to the new file group, which is on a separate disk from the table, makes sense. However, what about the clustered primary key index? What is best practice on this? What if it was a nonclustered primary key?

I hope this makes it more clear as to what I am asking.

Thanks,

Fraggle