Hash Join Operator
Produced: 04/05/2014 13:56:00
This is the last of the join operators and possibly the most powerful. It is possibly the most common join operator and, for some reason, one of the ones I hear people most saying they’d like to avoid. Personally I wouldn’t agree with that at all… although it does have a few down sides (which I’ll cover later in this post), it’s by far and away the best choice when joining large datasets together and should therefore be welcomed.

Merge Operator
Produced: 27/04/2014 17:42:00
Continuing with the posts on Join Operators I’m going to move on from the Nested Loop join and give a very brief explanation of the MERGE operator.

To be honest this is one of the join types I actually see the least out of all three. I’m not sure why this is, but that’s my experience. Doesn’t mean it’s a bad join type though as it’s very, very fast when it does appear. But it does have a couple of pre-requisites before SQL will consider it a viable option.

Nested Loop Operator
Produced: 20/04/2014 17:36:00
Join operators within SQL Server seem to be very misunderstood in general as people are forever telling me that they want a query to use this or that types of operator for the joins in their plan and they will even use hints to force these. Sadly it seems to come from a misunderstanding that certain types are better than others. This is not the case as they are all designed for a reason and each have their speciality.

In this case I’m going to cover a quick overview of the Nested Loop Join… what it is, how it works, and what it’s best suited for.