How to create indexs on a different file of Filegroup

Question

i'm using sqlserver 2008 R2

is there any way to specify that a table index is created on a sencondary file from a filegroup?

example:

"Primay" Filegroup has 2 files: Catalog.mdf and Indexes.ndf

how can i specify that my new index is created on the sencond file "Indexes.ndf" of "Primary" filegroup?

Answer 1

Indexes are created at the filegroup level. You can't get so granular as to specify a file within a filegroup. Sounds like you really should have made indexes.ndf a separate filegroup itself.

Answer 2

Short answer: No.

Longer answer: files in a data filegroup for SQL Server "load balance"; in other words, data is spread across the multiple files equally. If you want to designate a specific location for a file, you'll need to create a secondary filegroup on that new drive, and use it instead.

Question: Why do you want to do this? IMO, seperating indexes from data doesn't buy you much, and could cost you the benefits of using filegroups (like filegroup backup). Here's a good read on the subject: http://weblogs.sqlteam.com/dang/archive/2008/08/01/Are-you-a-DBA-Monkey.aspx