Using LOOP, MERGE, and HASH Join Hints

Ideally we just type INNER JOIN and let SQL Server do the work of figuring out the best way to resolve the query, but sometimes we need a little more performance and using join hints is one way to do that. In this lesson we'll show you the syntax for using LOOP, MERGE, and HASH join hints and a quick overview of when you might choose to use each of them. This is a more targeted technique than using OPTION (type JOIN) which overrides ALL the join types in a query.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Jeff on 9/22/2010

When giving a presentation on different types of joins a breif overview on the join types would be useful as well as a descritpion of when they are likely to used. Whilst this information is readily available for lookup, so is everything else in this presentation. My reason for viewing these presentations is to obtain a "Golden Nugget" or information that is distilled simply into a self-contained presentation. This manner in which this presentation was given meant that in order to take the information contained further, significant research would be involved to get a basic understanding of when this might be applicable.

Charlie Bruno on 9/22/2010

Nice.

Phil Rothenberg on 9/22/2010

The video was very good, but I could have used a little background on what each of the join hints does.

Alexander on 9/22/2010

good video

Dave on 9/22/2010

I think stressing caution when using join hints is also in order. By forcing a particular type of join, you're taking away the optimizer's ability to adapt to changing conditions such as an increase in the number of rows over the life of a database.

Jayaram on 9/23/2010

Excellent topic.

Craig on 9/23/2010

This video was really good for introducing options, and I understood your approach on "Sometimes you have to guess a little and just try stuff". Beleive me, mot my job is doing that. But I would love to know what SQL is doing with HASH and MERGE and LOOP. What does that mean? What's going on in the background? I would want to be able to take a test and reason with the query based on the data which options would help so I can apply them a little more surgically. More videos on this stuff, please! They are really good. I often don't look at execution plans because they don't mean anything. I would love help deciphering it in much more depth.

khurram bhatti on 9/25/2010

Good stuff.

Frankoid on 9/26/2010

Did not define the terms... Loop. Merge, Hash?

Nitin on 9/28/2010

Thanks Very much Andy.

Mohamed Irshad on 9/30/2010

coool

Mohamed Irshad on 9/30/2010

coool

Martin Miller on 10/7/2010

Thanks Andy.

sandip on 10/19/2010

excellent