I have a composite key for a table. I want to join on just one column of this key. Does that column need a separate index?

Question

Imagine I have a table with a composite primary key containing DateCode and AddressCode. I want to join that table with another table on just AddressCode. I know there will be a single index on DateCode combined with AddressCode, since that is the primary key. Should I also have an index on just AddressCode in this table just for the purposes of efficient joins to other tables only using the AddressCode as a foreign key? This is was what I would do in MySQL, though I'm not sure if Microsoft SQL Server handles this situation better automatically somehow.

Answer 1

After further research and experimentation, I have my own answer. Yes, a join on a column that is part of a composite key but is not the first element of that index (that is, "most significant member") requires a separate index. Without that index, performing a JOIN on that column requires a full scan of either the composite index or the table.

To clarify this further, if there is a composite index (such as is automatically created for a composite primary key) on three columns a, b, and c, if the index was created on a, b, c via

CREATE INDEX NewIndex ON Table(a, b, c)

then a is the most significant and c is the least. If the index was created on b, c, a, like so

CREATE INDEX NewIndex ON Table(b, c, a)

then b is the most significant. Since the index is ordered according to this significance, finding values indexed by the most significant component of a composite index requires only a trivial amount of additional effort in comparison to finding values indexed by that column alone (that is, it’s like looking for all integers that begin with “7” in an ordered list from 1 to 1000), whereas finding values indexed on less significant components of a composite index typically requires a full index scan (that is, it’s like looking for all integers that end with “7” in an ordered list from 1 to 1000).