Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    49

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Just recreate indexes:

    CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
    [ WITH < index_option > [ ,...n] ]
    [ ON filegroup ] --<<<<<<<<<<<<<<<<<<<<<<

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was meesing with

    Code:
    
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Posts
    49
    Will that SQL allow me to move the existing index to a different drive, and can you show me an example. Thanks.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I can't find a way to move the index across filegroups...

    Even EM does a drop and recreate...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •