Clustered index on two columns

Question

I've a many-to-many table, let's say:

PersonJob(personId,jobId)

with clustered index (personId,jobId).

The question is:

If somewhere in SQL I'll make a query like:

SELECT *
FROM PersonJob JOIN Job ON PersonJob.jobId = Job.jobId
.......

will it take advantage of that clustered index to find records with particular jobId in PersonJob table ? Or I would be better of creating new non-clusterd non-unique index on jobId column in PersonJob table?

Thanks Pawel

Answer 1

You will not have any advantage from the clustered index and your query would still need to scan all rows of the PersonJob table.

If the columns were reversed in your clustered index (jobID, personId) then you would take advantage of the index. Consider that a clustered index sorts the actual rows in your table by the values of the columns that form the index. So with a clustered index on (personId, jobID) you have all the rows with the same personId "grouped" together (in order of jobID), but the rows with the same jobID are still scattered around the table.