missing index on FKY

I am analyzing a database schema I inherited. I am noticing some tables which have FKY to
another table,
but don't have an index on those FKY columns. Coming from another RDBMS background, I find
it strange
that SQLServer even allows this. What is the reason in keeping index-creation a separate and
optional
task from creating a FKY.
Second question: Shouldn't it make sense to create index on FKY columns so that the joins
with the
parent table is faster and can also avoid table locks on the child table.
Yes, creating indexes on foreign keys can be beneficial to query
performance. However, the reason it is not automatically done is that
indexes are not necessary for enforcement of the constraint -- and there are
times that a DBA may not want to create an index. So you're given an
option...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:39oep6F60jstaU1@.individual.net...
> I am analyzing a database schema I inherited. I am noticing some tables
which have FKY to
> another table,
> but don't have an index on those FKY columns. Coming from another RDBMS
background, I find
> it strange
> that SQLServer even allows this. What is the reason in keeping
index-creation a separate and
> optional
> task from creating a FKY.
> Second question: Shouldn't it make sense to create index on FKY columns so
that the joins
> with the
> parent table is faster and can also avoid table locks on the child table.
>