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?