SQL Server: How does the type of an index affect a join's performance?

Question

If I'm am trying to squeeze every last drop of performance out of a query what affect does having these types of index's being used by my joins.

• clustered index.
• non-clustered index.
• clustered or non-clustered index with extra columns that may not be involved in the join.

Will I gain any performance if I go through and create clustered index's that only contain the columns involved in my joins and nothing else?

(I realize I may have to move the clustered index from another index(making that index non-clustered) since it can only have one.)

Answer 1

Will I gain any performance if I go through and create clustered index's that only contain the columns involved in my joins and nothing else?

Not as I understand. The point of a clustered index is that it then sorts the data on disk around that index (hence why you can only have the one), so if your join data isn't being sorted by those exact columns as well, I don't think it'd make any difference. Plus by putting data that might change (as opposed to the key) into the clustered index, you make it more likely that things will need rebuilding peridically, slowing the overall database down.

Sorry if this sounds a daft question, but have you tried running your query through the index tuning wizard? Not foolproof by any stretch but I've had some decent improvements from it in the past.