asked
How can i put Indexed tables on Seperate Disk
Hello All I want to keep all my indexed tables on seperate disk, can some one help me what are the steps to keep indexes on seperate drive.
I hope that you meant that the indexes for the tables should be kept on separate disk and not "indexed tables" on separate disks, because I hope every table have an index.
When you create the index, you can create it on a specific filegroup. First create a filegroup with files on the separate disk, then use CREATE INDEX with DROP_EXISTING clause to move them to the filegroup. Keep in mind that if you do this with the CLUSTERED index of a table, you will in fact move the actual table.
You will want to create a new file group and have it on the separate drive. Then you will recreate your clustered index and specify it to be created on the File Group. The clustered index will move the table, non clustered will just put the index in the file group. CREATE CLUSTERED INDEX [idx_rowid] on [dbo].[My_table_name] ([rowid] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [YOUR_FILE_GROUP] The syntax above is the scripted out version of creating an index. Your values will differ. Just take note of the last line where the index is being created "ON my FileGroup"
The steps to move the table are: 1. Create a FileGroup 2. use `ALTER TABLE` with the `MOVE TO` clause This will move the table (and I believe the clustered index). To move just the Index to a different filegroup, you will have to recreate it on the new file group
-1 The MOVE TO clause is specified in conjunction with DROP CONSTRAINT for the clustered index, and that is a bad idea. That will cause your table to become a heap on the specified filegroup.
