FK Position

Does it make any difference where we define the FKs in a table? I mean, do I speed up the query if I define it as the second field or the last one? What about the other fields, the ones that are not FKs, but are used as filters in a query?

Raul:

In general, none of this makes much practical difference to speed of execution. The two things that do matter are (1) do you have indexes on the foreign table in place that correspond to your foreign key and (2) does use of that index with a specific query also require a bookmark lookup for that specific query. If the foreign key has a correspondence to the clustered index of the other table -- and that is often the case -- then no bookmark lookup is necessary. If the foreign key has correspondence to a non-clustered index of the other table but there are fields in the other table that are referenced and are not part of the non-clustered index then a bookmark lookup will be necessary.

If a query references only a short list of records then the non-clustered index will often get used to optimize the query. However, if the query references a very large number of records in the foreign table the optimizer may "decide" that the cost of performing the random reads necessary to support bookmark lookups is too high. In these circumstances the optimizer will often opt to perform a table scan of the foreign table instead of of an index seek.

Bleah. Somebody please say this in a better way.

|||

Column position is virtually meaningless in defining PK's.

Column position is virtually menaingless except:

1. In UNIQUE and PRIMARY key constraints, and all indexes, order of columns has meaning.

2. In relatively rare cases, order of column conditions in a WHERE clause is involved (and only when the criteria is so large as to make it impossible for SQL Server to check all possible uses in a timely manner)

The position of a column in a table has no little if any bearing on performance, as it is just a representation of what is physically implemented in bits and bytes down in the physical table. If it were advantageous to reorganize the data on the page, the data could be reorganized by the storage engine without you knowing. So rest easy, it should make no difference at all.