Query Hints for Unions - CONCAT UNION, HASH UNION, and MERGE UNION

For most of the biggest decision we have to make when writing a union query is whether to use UNION or UNION ALL, the latter telling SQL not to check for or remove any duplicate rows in the final set which can sometimes be a nice performance gain. For those times when we're getting the performance we need we have the option to use the OPTION syntax to add a hint; CONCAT, HASH, or MERGE. In most cases the optimizer gets it right, but sometimes you'll have to experiment and try all three to determine the best hint to use in your situation. Remember that hints aren't a substitute for good indexes and good statistics!

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.

Charlie Bruno on 9/14/2010

Video was chopped, stopped to buffer several times. I am uncertain if it was my connection or not. Other than that, the info was very useful.

Dennis Allen on 9/14/2010

How about covering when to replace OR clauses with UNION queries?

E72B0A9423 on 9/14/2010

helpful info on using Profiler for performance analysis

Brian K McDonald on 9/14/2010

Very nice lesson Andy! It has been a while since I've used hints like this, but since I am on a project that is heavy into performance tuning right now, I'll try some of these when doing UNION's. Thanks, Briran

Holck on 9/14/2010

I really liked the pace in this video and the simplicity.

011A0851DF on 9/14/2010

Excellent shows by Andy

Mark Hathaway on 9/14/2010

This presenter is the best one on this site.

JumpstartTV on 9/14/2010

Very clear explanation, good video and audio.

amin on 9/14/2010

I learned something new today. Thanks

HD on 9/18/2010

Nice explanation. Thanks.

aaron williams on 9/20/2010

These hint videos need to have better examples of when hints would actually be useful. Just demonstrating the syntax without actually improving the query is not a good teaching tool.

SQLSharma on 9/21/2010

Awesome! Something new to learn more about! Thanks Andy.

Nitin on 9/28/2010

Awasome andy...thanks very much.

carylon anteau on 10/19/2010

The video is very nicely done, but is there a way to make the view larger?