indexes on foreign key columns?

I read recently in an ADO.NET book that I should create indexes on foreign
key constraints if they will commonly be used when joining. I can't
remember where, but I could swear that I had read somewhere else that
indexes were automatically created when you assign a foreign key constraint
to a column. What's the deal?
Joel LyonsHi Joel
You should create (yourself) indexes on referencing columns in a foreign key
constraint as it will improve things like the checking of the column when you
change a referenced value (for instance when you delete a row in the
referenced table). The referenced table will already have a primary key or a
unique index on the column being referenced.
John
"Joel Lyons" wrote:
> I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key constraint
> to a column. What's the deal?
> Joel Lyons
>
>|||Hi Joel
Indexes are NOT automatically created on foreign keys, and it is usually a
good idea to create them. Even if you don't specifically write joins between
foreign and primary keys, if you ever allow deletes or updates to the
primary keys, SQL Server does an internal join to find matching FK values,
and an index can help the performance of the data modification.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Joel Lyons" <joell_REMOVE_@.novarad.net> wrote in message
news:eTWbqWyIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key
> constraint
> to a column. What's the deal?
> Joel Lyons
>