STRING_AGG Limits: The 8,000 Byte Error, Ordering and Duplicates

Comma-separated lists look much cleaner with STRING_AGG than with the old FOR XML PATH pattern. Its convenient syntax still has three traps: an 8,000-byte result limit for a varchar input, no guaranteed order without WITHIN GROUP, and no built-in DISTINCT option. A small test shows each fix.

Reproduce the STRING_AGG Length Failure

The return type of STRING_AGG follows its input expression, not the destination variable. A varchar input that is not a max type can produce a varchar(8000) result. When a group grows beyond that limit, SQL Server raises error 9829 instead of quietly making a larger string. The test below creates enough short values to cross the boundary.

DROP TABLE IF EXISTS #Items;
CREATE TABLE #Items (ItemID int NOT NULL, Code varchar(20) NOT NULL);
;WITH n AS
(
    SELECT TOP (2000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemID
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT #Items (ItemID, Code)
SELECT ItemID, RIGHT('000000' + CONVERT(varchar(10), ItemID), 6)
FROM n;
-- The next query raises error 9829 when the list exceeds 8,000 bytes.
SELECT STRING_AGG(Code, ',') FROM #Items;

Run the failing SELECT separately so the later examples still execute. I prefer a reproducible failure over an unexplained CAST copied into every query. How large can the list become in the real workload, and is a single string the right output contract at that size?

Cast the Input to a Max Type

Cast the value inside the aggregate to varchar(max) or nvarchar(max) as appropriate. Casting the finished result is too late; aggregation already chose its return type. Choose varchar or nvarchar based on the source data and desired Unicode behavior. Check the consuming application too, because it can impose a smaller output limit.

SELECT STRING_AGG(CAST(Code AS varchar(max)), ',')
       WITHIN GROUP (ORDER BY ItemID) AS code_list
FROM #Items;

A max-type result is not a license to make endless lists. Large strings use memory, network bandwidth, and client processing time. If the consumer only needs to display the first twenty values, return rows or a bounded list instead of aggregating millions of IDs. The cast fixes the SQL type limit, not the overall design cost.

State the Order Explicitly

A table is an unordered set. STRING_AGG without an order clause can concatenate values in different sequences as plans and parallelism change. Add WITHIN GROUP (ORDER BY …) to define the output. Use a tie-breaker if the sort key is not unique, or two equal-ranked rows can still switch places.

SELECT STRING_AGG(CAST(Code AS varchar(max)), ',')
       WITHIN GROUP (ORDER BY ItemID) AS ordered_codes
FROM #Items;

The WITHIN GROUP ordering clause needs database compatibility level 110 or higher; at level 100 it fails with a syntax error. Check that setting before deploying a query to an older database. An ORDER BY outside the aggregate orders result rows, not items inside the string. When a list is used for a cache key or an audit record, stable internal order is part of correctness.

Remove Duplicates Before STRING_AGG Runs

The function has no DISTINCT argument. Select unique values in a derived table or CTE first, then aggregate them. If duplicates have different timestamps and you need first-seen order, calculate the minimum timestamp per value and order on that stable key. This example returns one copy of each Code.

WITH unique_codes AS
(
    SELECT Code, MIN(ItemID) AS first_item_id
    FROM #Items
    GROUP BY Code
)
SELECT STRING_AGG(CAST(Code AS varchar(max)), ',')
       WITHIN GROUP (ORDER BY first_item_id, Code) AS unique_list
FROM unique_codes;

Do not use DISTINCT over the whole source row if other columns differ; it will not remove repeated codes. Define which columns make an item the same. I inspect the number of source rows and unique values before assuming duplicates are harmless. The group step can be more expensive than aggregation on a large table, so index and measure the real key.

Compare STRING_AGG With the XML Method

The older pattern uses FOR XML PATH to concatenate, STUFF to remove the leading comma, and TYPE.value to decode XML entities correctly. Time it against the same input, ordering, and output type. The XML method is still useful to understand when maintaining legacy procedures, but a fair comparison must produce the same result.

SET STATISTICS TIME ON;
SELECT STRING_AGG(CAST(Code AS varchar(max)), ',')
       WITHIN GROUP (ORDER BY ItemID) AS result
FROM #Items;
SELECT STUFF
(
    (SELECT ',' + i.Code
     FROM #Items AS i
     ORDER BY i.ItemID
     FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),
    1, 1, ''
) AS result;
SET STATISTICS TIME OFF;

Run each several times and capture elapsed time, CPU, and actual plans. On small data, compile and client rendering can dominate. On larger data, sort and memory grants matter. The comparison is about your grouping and output size, not a universal claim that one syntax always wins.

Check Edge Cases Before Replacing XML

NULL inputs are skipped and do not produce a separator. If the list must include a placeholder, apply ISNULL or COALESCE to the input expression deliberately. Separator type must fit the expression type. Special characters need an end-to-end test because XML and plain string aggregation treat encoding differently. Keep a regression case with ampersands, angle brackets, Unicode, empty strings, and NULL.

I change one stored procedure at a time, compare exact output strings, and review the new plan. A shorter query is a welcome result, but identical business output is the first requirement. When the list exceeds a reasonable size, reconsider returning relational rows rather than forcing every consumer through one giant string.

Grouping Can Change the Limit

The error is per aggregate result, not the entire table. A query that groups by CustomerID can succeed for thousands of customers and fail only when one customer has an unusually long list. Test the largest group from real data. The declared width of the input expression also matters: converting Code to varchar(100) does not make the aggregate unlimited, while converting it to varchar(max) does. Check what the application does with the result when a formerly short list becomes a large object value.

If you need an ordered list per customer, include a deterministic order key within each group. An index on CustomerID and that key can reduce sorting work, though it adds write cost. Compare plans and memory grants. A spilling sort can dominate the aggregation time even after the 8,000-byte error is fixed.

Make the Timing Fair

Use the same rows and exact output order for both methods. The XML query needs TYPE.value to avoid exposing encoded text such as & to a caller expecting an ampersand. If one version removes duplicates and the other does not, the benchmark measures different work. Compare byte length and a hash of the final strings before comparing time.

I run each query more than once, ignore the first compilation-only surprise, and record CPU and reads as well as elapsed time. On a busy server, elapsed time includes unrelated waits. If the new syntax wins in a small test but spills at production size, the larger test is the one that informs the deployment.

Related reading on this blog: STRING_AGG Function to Concatenate Strings and CONCAT and NULL: SQL in Sixty Seconds #123.

STRING_AGG is not a substitute for type and order rules, it is the final aggregation after both are set.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.