Mike C (1/1/2008)
Oops you're right, I did miss that code block. I think the speedup is not really due to UDF overhead though, but rather because of FOR XML and xml data type optimizations. In the example I posted it still uses a UDF and it's still really fast.You can also do something like this with the data() node test, replacing the SUBSTRING and the inline string concatenation with a single REPLACE function call:
SELECT t1.SomeID, REPLACE ((SELECT t2.SomeCode AS 'data()'
FROM dbo.TestData t2
WHERE t1.SomeID = t2.SomeID
FOR XML PATH('')), ' ', ',')
FROM dbo.TestData t1
GROUP BY t1.SomeID;
Glad to bring you over to the XML dark side, even if you're just dipping your little toe in 🙂
Thanks again Jeff 🙂
Thanks for the great feedback and the code examples, Mike. Heh... "dark side" indeed... I hate XML 😛 Still, it seems that's the way a lot of folks have gone and I'm going to do some more heavy testing on some XML things... you know what they say... "Know thy enemy" :w00t:
Part of the testing isn't to avoid XML... I can see grand use for some of the "new" XML functions like the concatenation example, for example (even though one should severely limit the use of concatenation in SQL).
--Jeff Moden
Change is inevitable... Change for the better is not.