Specify default filegroup for indices?

Question

This is puzzling me - in SQL Server 2005/2008, I can specify the default file group which is then used for all data tables. I can also specify another filegroup for all BLOB type fields.

But I cannot figure out if and how I can specify a default filegroup for indices.....

My setup usually is: * primary filegroup - nothing but the system catalog - bare minimum * DATA filegroup - which I make the default for my database * INDEX filegroup for all indices * BLOB filegroup (optional) for all BLOB data * LOG (obviuosly)

Now if I create all by tables and indices manually by script, of course I can specify the appropriate file group for each. But is there a way / trick / hack to make it possible to specify where to put the indices? When I use SQL Server Mgmt Studio to e.g. designate a column as primary key on a table, it creates a PK_(tablename) index - but unfortunately, it sticks that into the DATA filegroup, and once it's there, it seems I cannot change it anymore (in the visual designer).

Am I missing something? Or has Microsoft just not provided such a setting??

Thanks for any hints, pointers, leads!

Marc

Answer 1

Hi,

If there is a clustered index on a table, the data and the clustered index always reside in the same filegroup. You cannot place the base table and clustered index on separate Filegroups. This is because the clustered index is the actual table data.

You can of course place non-clustered indexes in a different Filegroup from the table they reference.

You cannot specify two default Filegroups i.e. one for data and one for indexes.

Hope this clears things up for you but please feel free to pose further questions.

Cheers,John

Answer 2

You can create a clustered index on a filegroup other than where table resides,but the filegroup of table may automatically change to index's filegroup.