Unanswered: Moving indexes to their own disk drive
I am trying to find out if it is possible to move indexes to a separate filegroup/disk drive during database restore. I am trying this to see if it improves performance. Also if I cannot move the indexes during restore, how would I move them afterwards to a different filegroup/disk drive? Thanks in advance for all the help.
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1),Col2 char(1))
CREATE INDEX myTable99_IX1 ON myTable99(Col1)
ALTER TABLE myTable99 NOCHECK CONSTRAINT myTable99_IX1 ON Secondary
DROP TABLE myTable99
But can't get it to work....
Seems like you have to drop and recreate...I would have thought there might have been a move or alter or something....
One thing to remember, you can not move a clustered index to a drive (filegroup) that is different from the filegroup the data is on. In short, a clustered index is the data. As for an example, suppose you have two filegroups named UserData and IndexData. For table Customers you would have:
create (unique) index indexname on Customers (fields) on IndexData
alter table Customers add constraint pk_customers primary key (keyfield(s)) on UserData