Posted by Erland Sommarskog on 09/21/07 21:37

bobdurie@gmail.com (bobdurie@gmail.com) writes:
> If i create a simple table with a foreign key constraint, does it
> create an implicit index on that given ID?

In SQL Server, no.

> I've been told this is done in some databases, but i need to know for
> sure if SQL Server does it. Has anyone heard of this before, on any
> other databses perhaps?

I seem to recall having heard this about Sybase Anywhere.

> Heres an example of how the foreign key constraint is being added:
>
> ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
> [FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
> REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
> My initial testing seems to indicate adding an index on the foreign
> key column helps, but i need to know for sure. Any insight would be
> greatly appreciated!

Indeed, it is often a good idea to add indexes on foreign keys, as it
can speed up deletions considerably. And it is not uncommon to search
for data in a table on a foreign key. However, as always, you should
think twice, and not add indexes blindly. For instance, if you have a
country-code column in a address table, there is little reason to add
an index on that column, since you don't delete countries very often.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]