I know that joins of type Sort-Merge don't use nonclustered indexes, because they work by sorting both tables and then joining them.

But I've read recommendations to noncluster index all FKs, I believe that's for helping joining.

Is there then some type of join that uses nonclustered indexes?

  • If the nonclustered index is a covering index, then I believe that it would be a good candidate for a Sort-Merge, since the index, itself is sorted and there's no need to access the data pages of the table. – DeadZone Feb 3 '15 at 16:07
  • Oh, also, Paul White has an interesting article on the Merge Join. It might help you visualize the inner workings of the join. – DeadZone Feb 3 '15 at 16:47

Have a look at the query execution plan, that should tell you if it is using an index.

I don't know why a sort join couldn't use an index, keys can be read from the index and then sorted if required, which saves scanning the table.

  • Sorry, I meant nonclustered indexes. The Custered index is used, not nonclustered ones. – Hikari Feb 3 '15 at 13:58

I would put an index on the foreign key ( foreign keys don't automatically have an index created) and check out Kimberley Tripps article below. http://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.