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.