Budd (5/9/2014)
Simply looking for alternatives and better performance.I have gotten myself into a bad habit of only using UNION to avoid duplicates and had quickly brushed that thought away. As I think it over now I can see how the additional over head of filtering out duplicates could be costly, but now that you bring that to my attention I've run a little simple testing. When I run both UNION and UNION ALL at the same time and review the execution plans, they are both at 50%. If this is because the size of the selection is not enough to matter, how to I determine the size (aka volume of data) that it will take to make a difference between the 2?
But that is really another question and I am still open to ideas of different queries to produce the same results.
It is not about when or when not to use UNION ALL it is about knowing what is appropriate for the results. If you have a result set where duplicates are ok (or each row is unique) you should use UNION ALL. Not because it is acceptable here but because it will perform better. It performs better because it does not have to check for uniqueness. On a small dataset it isn't likely to make much (if any) measurable difference.
When tackling performance problems you need to look at the whole picture. There are a lot of factors that come into play when determining performance improvements.
If this was my query I would almost certainly use a UNION or UNION ALL for this type of thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/