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.Just recreate indexes:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ] --<<<<<<<<<<<<<<<<<<<<<<|||Look up ALTER TABLE
I think you can ALTER the contraint (The indexes) and change the file group
Never done it though...let me look into it...|||I was meesing with
USE Northwind
GO
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1),Col2 char(1))
GO
CREATE INDEX myTable99_IX1 ON myTable99(Col1)
GO
ALTER TABLE myTable99 NOCHECK CONSTRAINT myTable99_IX1 ON Secondary
GO
DROP TABLE myTable99
GO
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...
How big is the database?|||Will that SQL allow me to move the existing index to a different drive, and can you show me an example. Thanks.|||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|||I can't find a way to move the index across filegroups...
Even EM does a drop and recreate...|||if this is going to be a one time thing then i would invest the time and do it right and completely recreate the index on the new disk.
this would give you a fresh index on the new disk
and kids
dont forget your fill factor.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment