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:
A clustered index will be created on the primary key column
Id
on the tableMyTable
.Also, as can be inferred, a clustered index will be created on the table
Foo
for its primary key namedId
.
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:
- A clustered index for the primary key
Id
on the tableMyTable
. - A clustered index on the primary key
Id
on the tableFoo
.
Or will there be the following 3 indices:
- A clustered index for the primary key
Id
on the tableMyTable
. - A clustered index on the primary key
Id
on the tableFoo
. - A non-clustered index on the foreign key
Foo(Id)
on the tableMyTable
.
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
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