TheSQLGuru (4/13/2013)
Good points Jeff. I would definitely look at pre-populating the concatenated objects as temp tables. I had that in my notes I took while looking at alternatives for this and just missed putting it in the reply! :hehe:I looked at prepopulating those temp objects tho and I think in order to get the table key and the concatenated string is a double hit on the table for the methods I checked (except for cursor, which has is it's own issues obviously). SQL CLR into a temp object could still be best.
Agreed on the double hit but it would be one-time and that's far better than hitting the table 6 times for every row in the outer query.
While I agree that SQL CLR can work miracles with strings, I'm also aware that there is some overhead involved. I could certainly be wrong but I don't believe that SQL CLR would be much more effecient in this particular case.
--Jeff Moden
Change is inevitable... Change for the better is not.