Indexing Foreign Keys
Abba
[Across SQL2000 - 2008]
Though we are not facing any performance issue with any query, just curious
to know whether a non-clust index is necessary for a Foreign key in a OLTP
table? I noticed that in our database, some FKs do not have any indexes. I
did not yet check the respective query-plan but want to know if its a good
practice.
tia,
AbbA
Greg Low (MVP)
The lack of indexes related to foreign key references is a very common
problem I see with performance issues in SQL Server. We always go looking
for any foreign keys that aren't the left-hand component of at least one NC
index ie: a suitable covering index that covers common queries on that
foreign key is often more useful than just indexing the foreign key
column(s) alone.
As a simple example, while there's no need for an index to support the
foreign key constraint on a customerid in a salesorder table, what's the
chance that a query will want to find salesorders for a particular customer?
In general, it's pretty likely.
Regards,
Greg
"Abba" <sql_...@aaa.com> wrote in message
news:eedU3ANa...@TK2MSFTNGP06.phx.gbl...
Tom Cooper
cause performance problems. First, as Greg noted, it is often common that
you do queries on the child table based on the foreign key (for example,
select all the order detail lines belonging to a particular order). This
may be a reason you want an index. Of course, that is true of any column
you do lots of queries on. A reason that is particular to foreign key
columns is if you commonly delete rows in the parent table or change the
primary key in the parent table. When you do that, SQL must check the child
table to see if any rows had the foreign key value you just deleted/updated
in the parent. If you don't have an index, SQL will have to scan the entire
child table.
But there are cases where it is perfectly reasonable to not have an index on
the foreign key. If you don't query on the foreign key and you don't delete
the parent rows or change the primary key of the parent row, then you may
well not need an index. For example, if you deal with multiple currencies,
then you might have a currency code column on the order table to denote what
currency that order is priced in. You would want a foreign key constraint
to the currency table so that you would enforce that every order had a valid
currency. But it may well be true you rarely or never select all orders in
a given currency and rarely or never delete or update currency codes. It
that case, there may well be no performance penalty if you don't have an
index.
I noticed you asked whether a non clustered index is necessary. As an
aside, please note that there are cases where you want the index for the
foreign key to be your clustered index. (And then, of course, your primary
key index would be nonclustered).
Tom
"Abba" <sql_...@aaa.com> wrote in message
news:eedU3ANa...@TK2MSFTNGP06.phx.gbl...
sloan
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
I would say "most times you want an index on a FK".
However, the above URL will sometimes report these FK indexes as "bad
indexes" (alot of writes, very few reads) because (for example) there may be
another compound or covering index on that table.
........
Good luck.
"Abba" <sql_...@aaa.com> wrote in message
news:eedU3ANa...@TK2MSFTNGP06.phx.gbl...
--CELKO--
tables as if they were disjoint files. It repeats the same physical
data in the FK as the referenced PK/UNIQUE column(s)
Other SQLs (Sybase SQL Anywhere, etc) use a pointer chain from the
FKs back to the PK/UNIQUE column(s). This can be a fantastic
advantage. If you have no problems yet, then kludge some indexing.