0

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?

Flavio CF Oliveira
  • 5,235
  • 13
  • 40
  • 63

2 Answers2

2

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.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

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

Stuart Ainsworth
  • 12,792
  • 41
  • 46