We have multiple filegroups, that look something like this:
- PRIMARY (default)
When I create a table on filegroup
CREATE TABLE Person ( [PersonID] INT NOT NULL IDENTITY, [Identifier] NVARCHAR(64) NOT NULL, [SupervisorID] INT NULL ) ON app_data
and in the current setup (I haven’t made this) constraints will get added in 3 different ways
-- Primary keys are added to app_data ALTER TABLE Person ADD CONSTRAINT PK_Person PRIMARY KEY (PersonID) ON app_data -- Foreign keys are added to "nothing" ALTER TABLE Person ADD CONSTRAINT FK_Person_PERSON FOREIGN KEY (SupervisorID) REFERENCES Person(PersonID) -- Unique Indexes are added on app_index -- Sometimes also with constraint notation (but these are the same in background anyway) CREATE INDEX UX_Person_Identifier ON Person(PersonID) ON app_index
On what filegroup will the foreign key be created?
Should all constraints be created on
app_index? Or should only “custom” indexes be created on this filegroup?
PRIMARY KEY and
UNIQUE constraints have an optional
ON filegroup clause. That’s because these constraints always create an automatic index to support their uniqueness. The filegroup clause decides where this index will live, so in your case it should always be ‘app_index’.
You cannot specify a filegroup for other constraints such as
The metadata about all constraints (their name, table, columns etc.) is kept in system tables and thus is always in the PRIMARY filegroup (even if you change the default filegroup).
BTW: That’s why you always have to restore at least the
PRIMARY filegroup of a database. Without it, there is no metadata about the database content available to SQL Server. And this is why I recommend to use the
PRIMARY filegroup only for metadata – at least in enterprise scenarios. Have at least one other filegroup and set that as default. This way
PRIMARY remains the minimal possible size and runs the least risk of corruption.