Any intrinsic value in converting FOR XML's to STRING_AGG ?

[Isaac]

This post is mostly intended for people who are familiar with the 2 methods I'm talking about - as opposed to me having to provide a bunch of DDL that you can replicate or sample data - I mean that in the most respectful way but I think there will be those who know what I'm talking about without me taking the time to elaborate by providing sample data so am trying to save time

Okay ::

I have a lot of older code that uses the old method of a combination of STUFF and FOR XML in order to return data like value,value,value
I could convert this to a combination of Outer Apply and String_Agg (or a String_Agg directly inside a subquery on the Select list), but is there any inherent value likely to be gained from this? I know to a large extent "it depends", but assuming all else is equal - is there some intrinsic value like string_agg is going to be a lot faster or something? As far as we know ?

 

[cheekybuddha]

I always hated STUFF ... FOR XML. STRING_AGG() is much clearer in both its intention and syntax.

I'm not sure whether there is a performance benefit though - perhaps?

But if you have something that works, then no need to change it.

Anything new, however, it would make more sense now to use STRING_AGG() imho.

 

[cheekybuddha]

See a quick search result

 

[Isaac]

Yeah whenever I hear or think about readability versus not readability in SQL code, that awful for XML stuff is one of the top things that comes to mind. For being gibberish that's hard to understand!

I only ask because I have a long stored procedure that someone else wrote that it is my job to optimize. It's kind of hard to test it and although I am testing it, still I was kind of wondering about this because the old technique that I mentioned is used like 25 times in this code and I was just kind of wondering if there was any inherent value to updating it without running a test each time. Sounds like most likely not.

 

[AHeyne]

One point that should not be ignored is that in the case of the distribution of the product, it may happen that customers who use it may unfortunately still be using an older Microsoft SQL Server than 2017.
Therefore, 'FOR XML' is the more compatible variant.

 

[Minty]

I'm genuinely surprised there isn't a noticeable performance difference.
Personally, I would still change it as If I need to revisit it in the future I would better understand what was going on.