Nowt wrong with your query. I didn't enlarge your image however the optimiser will select the best plan it can (within certain provisos e.g. it selects the best plan within a time limit, it bases it's plan on available statistics etc.). As such - it is probable for this query that a hash join is better than a nested loop join. BOL illustrates such cases:
Originally Posted by SQL Server Bilble
A nested loops join is particularly effective if the outer input is quite small and the inner input is preindexed and quite large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are far superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.
Short of using hints (which is a bad idea unless you really know what you are doing and, arguably, not even then) all you can do is make sure that you write good, efficient SQL (as you have), ensure statistics are up to date and indexes are optimal. SQL Server does the rest.
Is it possible for me to still get the result of using nested loop join because i need to make a comparison between the execution time of using nested loop, hash & merge join. Will i get the result that i want if i use FORCE option?
I'm doing my FYP, and i need this results to be included in the report. Plus, i need to come up with a better algorithm, to retrieve data in distributed database. Thanks again for ur help.
I'm doing my FYP, and i need this results to be included in the report.
Aw - an RFH.
Ah well - I noticed a little gotcha that I didn't know re hints (unsurprising as you can probably tell I don't tend to use them) - see if you can spot it. Having reread BOL I think that it is referred to but it is rather under stated....