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

Solution

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.

Comments

[loczek]

I thought soo. But when I run "estimated execution plan" it showed, that that index is used anyway.

[UserControl]

What did it show - clustered index seek or clustered index scan (which is similar to table scan)? If you're interested in how to understand execution plans check this link: stackoverflow.com/questions/758912/…

[loczek]

Thanks for resource. Now I've played with different combinations of indexex and what @Damien_The_Unbeliever wrote seems to be partly right. When I added single column index on jobID column it replaced previous scan on clustered index. Cost of that step plunged from 45% to 10%, also numbers of rows scanned in SQL Server popup information window reduced from 11.000 to 10 :) - 10 is the number of imaginary jobs that query returns :) Anyway, before making any conclusion I'll read more about indexes and how to analyze execution plan. Thanks a lot.