Foriegn key indexes.

[melontek]

Hey everyone. Some great info in these posts!

I'm reviewing a not-so-great database and I've noticed that the foreign key columns on some of the tables have an additional index on them.

My question is, are those additional indexes necessary? Won't SQL Server 2000 use the index created for the PK on the source table when the FK is used in the where clause (assuming it's the only criteria in the where clause)?

Thanks!

 

[tlbroadbent]

The foreign key column should also be indexed for best performance when searching on that column. Creating a foreign key constraint doesn't create an index on the table and the Primary key index will not make searches of the foreign key table any faster. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[VogonPoet]

Terry,

I'm getting off topic here, but you said something very interesting in your response:

I just want to clarify something I think you said:

If you have a primary key on a table. The primary key is not used an index for queries (either in FK links or on queries of that table). If I want to use indexes for queries on the PK field(s) I must create a separate index.

Thanks,
Michael

 

[tlbroadbent]

Just to be sure we understand one another. A Foreign Key is a contraint. A Primary Key is a constraint and an index.

Table1
ID1 Int Primary Key
<more columns>

Table2
ID2 Foreign Key References Table1(ID1)
<more column>

Adding the Foreign Key constraint on the ID2 in Table2 does not index the column. The Primary Key column in table1 is indexed. The index on ID1 in Table1 will likely be used when ID1 is in the WHERE criteria.

The Primary Key on Table1 will not be used when searching Table2 even if JOINed to Table1. For best performance, you should create an index on the column ID2 if the column is used in JOIN or WHERE criteria. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[VogonPoet]

Terry,

Thank you for the clarification. That was my understanding as well. I misunderstood your first post.

Thanks for the clarification.

Michael