I have a single table that contains 4 billions rows that I plan on splitting up in to separate tables for each Quarter of the year. Then I want to create a partitioned view across all tables.

At the moment I have one filegroup for data (where most of the data resides) and one filegroup for non-clustered Indexes (I have been slowly migrating indexes across to this filegroup)

The data filegroup and non-clustered indexes filegroup are located on different disks.

The individual tables will have primary keys and non-clustered Indexes added to them. Where should I put these? (In terms of filegroups) Should I create filegroups for each table, for each type of index etc?

Note: I can't use Data Partitioning as we don't have Enterprise edition.

  • 1
    You're probably only going to see a performance gain if you have enough disks to keep them all separate, and even then you may be limited by other factors like controllers etc.
    – JNK
    Commented May 30, 2013 at 14:26

2 Answers 2


If you're going with traditional partitioned views with each table containing a different yearly quarter, then you have a few levels of separation here:

  1. Each Table
  2. Each Index on Each Table

So in other words, say you have 10 tables that the partitioned view accesses, and you have a clustered index and a nonclustered index on each table. In this case you are looking at 20 different data structures that you need to place accordingly.

As to where to put each of these will largely depend on your workload. For instance, if only the most recently two quarter tables get the most report queries and data modification, you'll want to ensure those are separate. But in the same respect, you could benefit from each of those tables having their indexes on separate disks.

I say disks here because filegroups are logical groupings of database data files. Even if you split each table and each index on their own filegroup (exaggerated to illustrate a point) but all the containing data files are on the same set of physical disks then you won't really see any underlying I/O performance gain. It's really just administrative at that point. You can't always divide the physical layer and the logical layer when squeezing performance out of a design.

So you need to visually lay out the physical disk separation, what type of common workload you will be receiving, and design your data placement in that manner.

  • Unfortunately, I did not get the performance gain I was hoping for. (For some queries, performance actually decreased). As you correctly pointed out, the physical disk layer is my bottleneck and this is something that I cannot change at the moment. Thanks for your answer! Commented Jun 1, 2013 at 6:57

I see various points to address here:

  • partitioning of a large table
  • filegroup placement of nonclustered indexes

Starting with the second one, separating non-clustered indexes might not be a good idea as a general purpose approach.
Having storage aligned (rows in a table, along with the indexes dependant upon those rows stored in the same file group) ensures that, if you want to backup or restore a partition, data and the corresponding indexes are kept together.

Other than this, if you will want to have a table with old data and one with just the most recent, you will want to work with SWITCH, which has among others the following requirements:

  • The data and index for the source and target tables must be aligned
  • Data cannot be moved from one filegroup to another
  • The OP explicitly stated that Enterprise Edition is not available. I agree about not separating indexes from the data, though.
    – Jon Seigel
    Commented May 31, 2013 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.