I will admit I am no expert in using STUFF + FOR XML PATH! I am trying to improve a query that gets executed multiple times and 48% of that execution time is in the FOR XML PATH part of the query. We are running SQL Server 2017 so I am trying to replace it with the new STRING_AGG function instead. Below are my two attempts at each. I am getting different results for each. I am trying to get the same results so I can just replace the FOR XML PATH code and hopefully see some performance gains. Hopefully this is easy to follow as I have pulled this out of a much larger query just to isolate these calls.
--Query
--XML PATH
Select
STUFF(','+(
select distinct im.shortcode FROM issuer_metadata im FOR xml path('')
, type ).value('.', 'varchar(max)'),1,1,'')
--STRING_AGG FUNCTION
select distinct string_agg(im.shortcode,'')
FROM issuer_metadata im
--Results: Note different results
--XML Path : BACBCSBMOBNPBNSCCIBCCSGSHSBCIFCJPMMSNTXRBCSEKSGTC2TDUBSWFBWFC
--String_Agg: BCSBMOCGSHSBCJPMMSWFBBACBCSBMOBNPBNSCCIBCCSGSHSBCIFCJPMMSNTXRBCSEKTC2TDUBSWFCBCSCCSGSHSBCJPMMSRBCSGTDUBSBNP