CODEWITHSUNDEEP
sqlsql-serverdatabasereferencesql-server-2014
Water Cooler v2
Water Cooler v2

Reputation: 33880

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

If I have a table like so:

CREATE TABLE MyTable
(
    Id INT PRIMARY KEY IDENTITY(1, 1), 
    FooId INT NOT NULL FOREIGN KEY REFERENCES Foo(Id),
    Data NVARCHAR(10) NOT NULL
);

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.

Upvotes: 7

Views: 1303

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176064

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

CREATE TABLE MyTable(
  Id int PRIMARY KEY IDENTITY(1, 1), 
  FooId int NOT NULL FOREIGN KEY REFERENCES Foo(Id),
  Data nvarchar(10) NOT NULL,
);

exec sp_helpIndex 'MyTable'

index_name  index_description   index_keys
PK__MyTable__3214EC0742A69968   clustered, unique, primary key located on PRIMARY Id

Explicit index creation:

CREATE TABLE MyTable (
  Id int PRIMARY KEY IDENTITY(1, 1), 
  FooId int NOT NULL FOREIGN KEY REFERENCES Foo(Id),
  Data nvarchar(10) NOT NULL,
  INDEX FK_FooId nonclustered(FooId)  -- inline syntax
); 

exec sp_helpIndex 'MyTable'

index_name  index_description   index_keys
FK_FooId    nonclustered located on PRIMARY     FooId
PK__MyTable__3214EC0779B032FB   clustered, unique, primary key located on PRIMARY Id

db<>fiddle demo

Upvotes: 7

Related Questions