Skip to content
Advertisement

Is a non-clustered index implicitly created for each foreign key in a table?

If I have a table like so:

The following observations may be made:

  1. A clustered index will be created on the primary key column Id on the table MyTable.

  2. Also, as can be inferred, a clustered index will be created on the table Foo for its primary key named Id.

Question:

Will there also be created an index for the foreign key Foo.Id on the table MyTable?

In other words, is a non-clustered index created implicitly for every foreign key on the dependent table.

In still other words, will the total number of indices created in this schema be 2 as follows:

  1. A clustered index for the primary key Id on the table MyTable.
  2. A clustered index on the primary key Id on the table Foo.

Or will there be the following 3 indices:

  1. A clustered index for the primary key Id on the table MyTable.
  2. A clustered index on the primary key Id on the table Foo.
  3. A non-clustered index on the foreign key Foo(Id) on the table MyTable.

My question pertains to Microsoft SQL Server 2014.

Advertisement

Answer

No, it is not created automatically. It is a good practice to create it manually:

The Benefits of Indexing Foreign Keys

Unlike primary key constraints, when a foreign key constraint is defined for a table, an index is not created by default by SQL Server.
However, it’s not uncommon for developers and database administrators to add them manually

Explicit index creation:

db<>fiddle demo

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement