I set up this table:
However both the table and index were on Filegroup FG1. Why?
Since the clustered index is the table within SQL Server, where you specify storage of the clustered index overrides where you specified storage of the table itself. This would also come into play if you created the table without the clustered index and then added the clustered index with the storage specification. It would "move" the table to where the cluster is stored since the cluster is, in fact, the table itself.
answered Oct 20 '09 at 08:42 AM
Grant Fritchey ♦♦
Because when you specify a filegroup for a table, you are specifying where the clustered index for that table goes, or where the heap goes if you have not specified a clustered index.
The specification at the table level takes precedence.
Also interestingly, if you had created the table without the constraint, then created the constraint, you would find that when you re-scripted it, it would claim that the ON clause for the table was FG1.
So, if you did this:
Then when you scripted the table out, the script would be this:
Notice how now the table-level filegroup specification is FG1 - because that's where the clustered index resides. As far as SQL Server meta-data is concerned, there is no such thing as a table-level filegroup for main data, simply a filegroup which contains either the clustered index or heap.