14

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

dragonfly
  • 17,407
  • 30
  • 110
  • 219
  • 1
    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 Mar 25 '11 at 09:20
  • 1
    If you'd like to learn more about indexes: have a look at my [SQL Indexing tutorial (also for SQL Server)](http://use-the-index-luke.com/). The page on [multi-column indexes](http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys) explains why your query cannot make efficient use of the (clustered) index you have. – Markus Winand Mar 26 '11 at 10:01

1 Answers1

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.

Paolo Falabella
  • 24,914
  • 3
  • 72
  • 86
  • 1
    I thought soo. But when I run "estimated execution plan" it showed, that that index is used anyway. – dragonfly Mar 25 '11 at 08:38
  • 2
    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: http://stackoverflow.com/questions/758912/how-to-read-a-execution-plan-in-sql-server – UserControl Mar 25 '11 at 08:40
  • 1
    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. – dragonfly Mar 25 '11 at 12:22