Do foreign keys generate implicit indexes?

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? 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?

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!

Bob

Re: Do foreign keys generate implicit indexes?

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Do foreign keys generate implicit indexes?

>> I've been told this is done in some databases, .. <<

Yes, but better. Sybase SQL Anywhere (nee Watcom SQL) builds links
from all the FK references to the single PRIMARY KET/UNIQUE occurence
in the referenced table. Saves space, pre-joins tables for speed and
makes DRI actions both easy and fast.

SQL Server is still thinking in terms of "table = file" instead of
"table is part of a whole schema" and that "record =row" instead of
"row is made up of columns". Stonebreaker had a recent blog on column-
oriented design over contigous storage model.

Re: Do foreign keys generate implicit indexes?

On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@gmail.com"
wrote:

Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.

-Tom.


>If i create a simple table with a foreign key constraint, does it
>create an implicit index on that given ID? 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?
>
>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!
>
>Bob

Re: Do foreign keys generate implicit indexes?

> Check with sysindexes to see if SQL Server does this too.

As Erland mentioned, SQL Server does not automatically index foreign key
columns. That task is left to the discretion of the DBA, who might choose
not to index the foreign column(s) due to low cardinality and static data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tom van Stiphout" wrote in message
news:8qqbf3190hk4cis52502s9th2ebsjjfpd5@4ax.com...
> On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@gmail.com"
> wrote:
>
> Microsoft Access does this, when you create a relationship between two
> tables.
> Check with sysindexes to see if SQL Server does this too.
>
> -Tom.
>
>
>>If i create a simple table with a foreign key constraint, does it
>>create an implicit index on that given ID? 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?
>>
>>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!
>>
>>Bob

Re: Do foreign keys generate implicit indexes?

Also, analyse your query requirements then apply an Indexing Strategy

--

Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL




wrote in message
news:1190404274.471197.197240@n39g2000hsh.googlegroups.com.. .
> If i create a simple table with a foreign key constraint, does it
> create an implicit index on that given ID? 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?
>
> 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!
>
> Bob
>

Re: Do foreign keys generate implicit indexes?

On Sep 24, 3:25 am, "Jack Vamvas" wrote:
> Also, analyse your query requirements then apply an Indexing Strategy
>
> --
>
> Jack Vamvas
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com/SQL
>
> wrote in message
>
> news:1190404274.471197.197240@n39g2000hsh.googlegroups.com.. .
>
> > If i create a simple table with a foreign key constraint, does it
> > create an implicit index on that given ID? 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?
>
> > 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!
>
> > Bob

Thanks for all the responses on this, its much appreciated!!! I also
found this article which makes me realize other people have had the
same misconceptions as me :)
http://www.sqlskills.com/blogs/kimberly/2004/10/25/WhenDidSQ LServerStopPuttingIndexesOnForeignKeyColumns.aspx