Sql-server – On which filegroup will constraints be created by default when adding them to a table on a filegroup and how should they be separeated

constraintdatabase-designfilegroupssql server

We have multiple filegroups, that look something like this:

PRIMARY (default)
app_data
app_index

When I create a table on filegroup app_data

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? app_data or PRIMARY?

Should all constraints be created on app_index? Or should only "custom" indexes be created on this filegroup?

Best Answer

Only 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 FOREIGN KEY, CHECK or DEFAULT.

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.

Related Solutions

Sql-server – With MS SQL Server are the generated constraint names predictable

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar) 
  REFERENCES dbo.foo(bar));

Sql-server – Partitioned Views – Storage of Indexes

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:

Each Table
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.

Best Answer

Related Solutions

Sql-server – With MS SQL Server are the generated constraint names predictable

Sql-server – Partitioned Views – Storage of Indexes

Related Question