0

I know that foreign keys are not indexed by default, but if the original reference is an index, will a FK also be an index - i.e. is indexing transitive?

Example:

  • Table1 has a non-clustered composite index on columns(A,B)
  • Table2 has two foreign keys with references to Table1.A and Table1.B

Will running a SELECT on Table2.A_FK or Table2.B_FK offer indexed performance, i.e. ~O(log[n]) instead of non-indexed O(n)? In SQL Server Management Studio, object explorer will not list these two foreign keys as an index for Table2.

w128
  • 4,680
  • 7
  • 42
  • 65

1 Answers1

1

In short - no. If you want the field of table2 to also be indexed, you should explicitly index it yourself (and as you stated, this is probably a good idea)

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Thank you. One thing I don't understand: why would someone want to have a non-indexed foreign key that references an indexed column (such is the case in my situation - I didn't design the database)? Wouldn't automatically creating an index on the foreign key point to the original (referenced) index anyway, i.e. no duplication etc.? – w128 Jan 16 '14 at 09:08
  • 2
    No - an index on the referencing table would index the values in that table, not the values it's pointing to. I can't however, off the top of my head, come up with a usecase where you would not want to index a column that serves as a foreign key. Perhaps if its cardinality is very low or something down those lines. – Mureinik Jan 16 '14 at 09:13
  • 1
    @Mureinik: well, you might want to create an index that contains not only the FK column - but also a second, third column and possibly some included columns, too, to cover some queries. You'll still have an index on your FK column (which is a good thing in general) - but maybe not *only* on your FK column – marc_s Jan 16 '14 at 10:18