STRING_AGG vs FOR XML PATH: the fast old trick is the wrong one

Before SQL Server 2017 there was no built-in way to concatenate the rows of a group into one delimited string, so a generation of us learned the FOR XML PATH('') + STUFF incantation by heart. Then STRING_AGG arrived and made it a one-liner. The old hands still reach for the XML trick out of muscle memory — and some insist it’s faster. So: is it? And does the answer depend on the shape of your data?

I benchmarked both over 100,000 rows on SQL Server 2025, varying the group density — many small groups (10 members each) through few large ones (1,000 members each) — with the grouping key clustered so the XML pattern’s correlated subquery does an index seek per group (the realistic way people run it, not a strawman). Timing methodology is the same as the rest of the series; numbers are ms per whole query, minimum of eight interleaved passes, and they’re clean (sub-5ms spread). But the headline isn’t a number — it’s a correctness trap.

The trap: there are two XML PATH variants, and the fast one is wrong

The compact version of the trick that everyone copy-pastes is entitization-unsafe:

-- the fast one... that mangles your data
STUFF((SELECT ',' + member FROM t WHERE t.gid = g.gid FOR XML PATH('')), 1, 1, '')

FOR XML PATH produces XML, and XML escapes markup characters. The moment a value contains &, <, or >, this returns corrupted output. Straight from the benchmark’s correctness probe, the same group concatenated three ways:

Method	Output
`STRING_AGG`	`a&b<00001,a&b<00002,…`
`FOR XML PATH('')` (raw)	`a&b<00001,a&b<00002,…`
`FOR XML PATH(''), TYPE).value(…)`	`a&b<00001,a&b<00002,…`

The raw variant turned a&b< into a&b<. Any URL with a query string, any “Smith & Sons”, any <tag> — silently entitized. The correct XML approach needs the , TYPE).value('.', 'NVARCHAR(MAX)') dance to decode it back:

-- the correct one... that costs more
STUFF((SELECT ',' + member FROM t WHERE t.gid = g.gid
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Keep those two apart, because they perform completely differently — and the cheap one is not a real option.

The numbers

Milliseconds per query (minimum across trials), by members-per-group:

Method	10/group (10k groups)	100/group (1k groups)	1000/group (100 groups)
`STRING_AGG`	92.6	89.2	89.2
`STRING_AGG` ordered	91.3	87.6	90.0
`FOR XML PATH` raw (wrong output)	112.7	82.1	76.7
`FOR XML PATH` safe `.value()`	272.2	111.8	33.6
`FOR XML PATH` safe + ordered	278.1	110.9	32.4

Three things jump out.

STRING_AGG is flat and boring — in the best way. ~90 ms whatever the group shape, with a tiny variance. It scans once, hash-aggregates, concatenates. Its cost tracks the row count, not the group geometry, so it’s predictable: you can reason about it without knowing how your data clumps. Adding WITHIN GROUP (ORDER BY …) was essentially free here.

The correct XML variant’s cost is dominated by per-group XML parsing. Look at how violently it swings: with 10,000 small groups it’s 272 ms — three times slower than STRING_AGG, because every group pays to build a little XML document and then parse it back out with .value(). With 100 big groups it’s 33 ms — 2.7× faster than STRING_AGG, because there are only 100 extractions and XML’s internal buffer handling is genuinely good at assembling long strings. The crossover is around the hundred-members-per-group mark on this box.

The only XML variant that broadly beats STRING_AGG is the one with the data-corruption bug. The raw, entitizing version is the one that looks competitive at 77–113 ms — and it’s the one you can’t safely use. Once you pay for correctness with .value(), you’re faster than STRING_AGG only in the few-large-groups regime.

So which should you use?

Default to STRING_AGG. It’s correct out of the box (no entitization, no STUFF to trim the leading delimiter), it’s one readable line, ordering is built in via WITHIN GROUP, and its performance is flat and predictable across data shapes. For the overwhelmingly common case — lots of smallish groups — it’s also simply faster than the correct XML alternative.
Reach for FOR XML PATH only in the narrow niche it still wins: a small number of very large groups, where you’ve measured it and you need the throughput. And if you do, use the , TYPE).value('.', 'NVARCHAR(MAX)') form — never the bare one — or you will ship & into someone’s report.
Mind STRING_AGG’s one sharp edge. It errors (msg 9829) when a group’s result exceeds 8,000 bytes unless the input is a LOB type. The fix is to cast the argument: STRING_AGG(CAST(member AS NVARCHAR(MAX)), ','). In the probe, the raw VARCHAR(100) form blew up at 8000 bytes; the NVARCHAR(MAX) cast concatenated 13,985 characters without complaint. (The benchmark uses the MAX-cast form throughout, so the comparison is apples-to-apples.)

The honest caveats

Shared host, so trust the shape of these curves over the absolute milliseconds — but the shape is the whole point here, and it’s robust: STRING_AGG flat, safe-XML group-count-sensitive, raw-XML fast-but-wrong. One clustered grouping key, one string width (~20 chars), NVARCHAR(MAX) accumulation throughout. If your strings are much wider or your groups much larger, re-measure — but start from STRING_AGG and make the XML trick prove it deserves to come back out of retirement.

Part of a series of measured T-SQL answers: + vs CONCAT, ISNULL vs COALESCE, IIF vs CASE, SET vs SELECT vs DECLARE, temp-table cleanup. Methodology: how to benchmark a microsecond. Harness + raw data reproducible against a disposable SQL Server 2025 container — get in touch.