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,
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.