are foreign Keys indexed Automatically?

Foreign keys are not indexed unless they are part of a concatenated key.

Adamus

AdamusTurner at 2007-9-13 >

No, they are not, but the answer as to whether or not to add indexes is a fairly complex question that is usually a case by case question.

For a domain relationship where the parent table only has a few values, and the child has lots, it could be a worthless index (unless the key is used independent of the other columns in the table)

For normal parent child relationships (like a invoice and line item, or invoice and customer), it is very often the case that it is worthwhile to have an index (unless you only have two customers and they do lots of business with you.) Of course, there are situations where they can be useful, (like if most of the table contains one of the values, and the other 10 are used by 1 percent of the rows.)

SQL Server maintains stats on all columns as it sees a need to answer queries and estimate cardinality, so it isn't worth it just for that reason. On smaller databases, it won't much matter either way, so it is probably good to add them in all cases other than domain relationships.

LouisDavidson at 2007-9-13 >