foreign key and index issue

Question

Hello everyone,

I am using SQL Server 2008 Enterprise. I have a table and one of its column is referring to another column in another table (in the same database) as foreign key, here is the related SQL statement, in more details, column [AnotherID] in table [Foo] refers to another table [Goo]'s column [GID] as foreign key. [GID] is primary key and clustered index on table [Goo].

My question is, in this way, if I do not create index on [AnotherID] column on [Foo] explicitly, will there be an index created automatically for [AnotherID] column on [Foo] -- because its foreign key reference column [GID] on table [Goo] already has primary clustered key index?

CREATE TABLE [dbo].[Foo](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [AnotherID] [int] NULL,
    [InsertTime] [datetime] NULL CONSTRAINT DEFAULT (getdate()),
 CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Foo]  WITH CHECK ADD  CONSTRAINT [FK_Foo] FOREIGN KEY([Goo])
REFERENCES [dbo].[Goo] ([GID])
ALTER TABLE [dbo].[Foo] CHECK CONSTRAINT [FK_Foo]

thanks in advance, George

Answer 1

No. SQL Server does not and never has automatically created indices on foreign key columns. It's a wide spread urban myth - but it's nothing more than that - a myth.

But it's an accepted best practice to do so - so that's one of the most basic recommendations for index tuning - put indices on your foreign key columns.

See Kimberly Tripp's excellent When did SQL Server stop putting indexes on foreign key columns? blog post for more background info.

Answer 2

No, there will not be an index created, you have to do it yourself