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

The leaf pages of the clustered index actually contain the row data - that's where the data rows are stored for a table with a clustered index. So unless there's a non-clustered index that includes all relevant columns for a query (called a covering index), every query will always have to access the clustered index at some point. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 25, 2011 at 9:20
If you'd like to learn more about indexes: have a look at my SQL Indexing tutorial (also for SQL Server). The page on multi-column indexes explains why your query cannot make efficient use of the (clustered) index you have. — Markus Winand
– Markus Winand, Commented Mar 26, 2011 at 10:01

Paolo Falabella · Accepted Answer · 2011-03-25 08:27:52Z

26

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.

edited Mar 25, 2011 at 8:27

answered Mar 25, 2011 at 8:21

Paolo Falabella

26k3 gold badges79 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dragonfly Over a year ago

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

UserControl Over a year ago

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/…

dragonfly Over a year ago

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.

Collectives™ on Stack Overflow

Clustered index on two columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related