Menu
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
407
Short: Although SQL Server allows us to add up to 16 columns to the clustered index key, with maximum key size of 900 bytes, the typical clustered index key is much smaller than what is allowed, with as few columns as possible.
SQL Server CREATE CLUSTERED INDEX syntax First, specify the name of the clustered index after the CREATE CLUSTERED INDEX clause. Second, specify the schema and table name on which you want to create the index. Third, list one or more columns included in the index.
If yes then how does it work internally? As per my understanding the leaf level of the clustered index are the actual data pages itself, and it stores entire row include with the key column (on which index is created). By definition a clustered index includes all columns... so there are none left to include.
Clustered Index. A clustered index is an index which defines the physical order in which table records are stored in a database. Since there can be only one way in which records are physically stored in a database table, there can be only one clustered index per table.
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.
153
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
