Moving Indexes to

[Vish]

Hi,

I need to separate the data and indexes onto different drives.Currently they r all on same drive..Now i need to move indexes onto a separate drive.There are no file groups now except the default.

I plan to create a file group on the new drive.I know if i Drop clustered indexes and recreate them on the new file group,it will separate the data pages from index pages.But the data pages need to be on the same drive as it is now.So i need to move index pages or non-clustered indexes only on to the new file group on the new drive.Even this can be done by choosing a different file group for the non clustered index in Enterprise manager.But this very cumbersome if there exists many tables as is the case now like 300 tables..

My Question is,

1.Is there any way to script only indexes so that it includes drop and create commands to be created on new file group???

2.Is there any other way to do this..

Please let me know if any body worked on this before..I really appreciate if some body can give me info on this..

thanks
Vish

[Ray Miao]

No, data page will stay with cluster index page.


------------
Vish at 12/4/00 3:25:13 PM

Hi,

I need to separate the data and indexes onto different drives.Currently they r all on same drive..Now i need to move indexes onto a separate drive.There are no file groups now except the default.

I plan to create a file group on the new drive.I know if i Drop clustered indexes and recreate them on the new file group,it will separate the data pages from index pages.But the data pages need to be on the same drive as it is now.So i need to move index pages or non-clustered indexes only on to the new file group on the new drive.Even this can be done by choosing a different file group for the non clustered index in Enterprise manager.But this very cumbersome if there exists many tables as is the case now like 300 tables..

My Question is,

1.Is there any way to script only indexes so that it includes drop and create commands to be created on new file group???

2.Is there any other way to do this..

Please let me know if any body worked on this before..I really appreciate if some body can give me info on this..

thanks
Vish

[Vish]

Hi ray,

U r right..data page will always be with clutered index page i.e leaf page.My problem is,i have to move the index pages of non-clustered indexes or non-leaf level pages to a different drive.

Please let me know if u have the solution for this..

thanks
venkat


------------
Ray Miao at 12/4/00 4:00:18 PM

No, data page will stay with cluster index page.


------------
Vish at 12/4/00 3:25:13 PM

Hi,

I need to separate the data and indexes onto different drives.Currently they r all on same drive..Now i need to move indexes onto a separate drive.There are no file groups now except the default.

I plan to create a file group on the new drive.I know if i Drop clustered indexes and recreate them on the new file group,it will separate the data pages from index pages.But the data pages need to be on the same drive as it is now.So i need to move index pages or non-clustered indexes only on to the new file group on the new drive.Even this can be done by choosing a different file group for the non clustered index in Enterprise manager.But this very cumbersome if there exists many tables as is the case now like 300 tables..

My Question is,

1.Is there any way to script only indexes so that it includes drop and create commands to be created on new file group???

2.Is there any other way to do this..

Please let me know if any body worked on this before..I really appreciate if some body can give me info on this..

thanks
Vish