TheSQLGuru (6/21/2011)
You should absolutely positively NOT use the CTE for this scenario unless you are guaranteed to have VERY few rows split out AND you do not have any signficant data skew. If you have either or both of those you will get screwed with a bad plan at least some of the time. Either a nested loop index seek plan with a kajillion rows or a scan/hash plan with a few rows. The optimizer can't have any idea how many rows are coming out of the split. Nor can the optimizer have statistics on the VALUES of the row(s) coming out of the split.
Kevin,
I thought about that before I tested the CTE version. For the small number of table elements it worked just fine. As I stated in the article, testing should be done to check performance. I realize that CTE's don't have statistics.
In a production environment where it's likely that there could be many elements I probably wouldn't even bother to bench mark the CTE version. I just brought it up as a possibility.
Thanks for your comments. I appreciate your input.
Todd Fifield