Index structure for Join and Where clause

Transact-SQL
1

I join two tables on a field (ProductId) and have a Where clause on another field (StatusId), like this:

Select p.Description From Products p Inner Join ProductStatus ps On P.Id=ps.ProductId Where ps.StatusId=5

For an index on ProductStatus table, what is the best field order: ProductId and then StatusId or StatusId and then ProductId ?

Is a Composite Primary Key made from those two columns (in the order resulted from the previous auestion) more efficient ?

Thank you

2

( StatusId, ProductId )

3

Thank you very much, @ScottPletcher !

Can you help me with the second question too, pls? i.e. Is a Clustered Composite Primary Key (StatusId,ProductId) efficient? (The question is based on the fact that leaf nodes in primary indexes is the data itself and it seems a composite index is useless).

Thank you again

4

In general, you don't want a value in the clustering key that changes (such as Status/StatusId). Although at times it's still the most efficient.

In this specific case, if you don't need other columns from the ProductStatus in the query (there are no other columns shown now), then just a nonclustered index on ( StatusId, ProductId ) is fine. If you have a very limited number of additional column(s) you need, INCLUDE those column(s) in the nonclus index:
( StatusId, ProductId ) INCLUDE ( OtherReferencedColumn )

Only if you use many of the columns in the main table in the query with StatusId=5 would you potentially have to change the clustered index.

1 Like