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.

Local Variable Gotcha
Produced: 13/04/2014 17:11:00
We all use local variables and in many cases they’re invaluable… however, there is a lesser known (in my experience anyway) gotcha with them which can badly affect your queries and leave you wondering why performance has unexpectedly dropped.

Sadly there’s no “just use this” style of cure for this problem, but there are things that you can do to alleviate the impact this issue can have on your code.

Using Duration in Profiler and SSMS for Query Tuning
Produced: 06/04/2014 16:57:00
This is a quick post regarding a couple of things I found someone doing the other day when using Profiler and SSMS to tune one of his queries. Basically he was getting frustrated because he had been making changes to his code which he believed would speed up his query and yet he wasn’t getting anything consistent out of either SSMS or Profiler to prove this.