ramana3327 (12/25/2013)
Hi what is the main difference b/n mergejoin, hash join & nested loop join? In which cases those will form? Please tell me which is the very bad performance issue. i.e. most costly operator.
Looking at your other posts and to be honest, you're asking a whole lot of questions where the answers are easily found in "Books Online". For example, if you search for "Merge Join" in "Books Online", not only will you find something that tells you what it is, but you'll also get some hints as to when such a join could be a performance problem. Here's the last sentence in that entry.
Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.
If you don't specifically know what "Books Online" actually is, open SSMS, press the {f1} key, and you're there. I strongly recommend that you spend some time looking for the answers to your questions there so that you become more familiar with the tool especially since it's usually much quicker to find your own information than it is to post to a forum.
--Jeff Moden
Change is inevitable... Change for the better is not.