Resolving STRING_AGG 8000 Bytes Limit Error

Problem Statement

When using SQL Server's STRING_AGG function to concatenate text values, you might encounter the error:
"STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation."

This occurs because:

• STRING_AGG has an 8000-byte limit for varchar/nvarchar outputs
• Aggregated results exceeding this limit cause truncation
• Default types without explicit length restrictions use the 8000-byte limit

Understanding the 8000-Byte Limit

Why this limit exists

• SQL Server optimizes storage for common cases (under 8000 bytes)
• varchar(MAX) and nvarchar(MAX) bypass this limit using LOB (Large Object) storage
• Without explicit casting, input determines the output type

When aggregating long text fields or combining many rows, the calculated result can easily surpass this threshold.

Solution: Convert Values to MAX Length

The proper solution is casting each concatenated value to varchar(MAX) or nvarchar(MAX) to overcome the 8000-byte limitation:

Using nvarchar(MAX) (Unicode Support Required)

SELECT 
    c.id,
    c.bereichsname,
    STRING_AGG(CAST(j.oberbereich AS nvarchar(MAX)), ',') AS oberBereiches
FROM stellenangebote_archiv AS j
JOIN bereiche AS c ON j.bereich_id = c.id
GROUP BY c.id, c.bereichsname;

Using varchar(MAX) (ASCII Only)

SELECT 
    Employee_Name, 
    STRING_AGG(CAST(Project_Name AS varchar(MAX)), ',') AS all_project_names
FROM dbo.employee_360
GROUP BY Employee_Name;

Key Considerations

1. Consistent Data Types:
   Always match the CAST type to your source data's character type (nvarchar for Unicode, varchar for ASCII)

2. Performance Impact

   LOB Storage Considerations

   While varchar(MAX)/nvarchar(MAX) handle large strings, they have performance trade-offs:

   • Slightly slower processing than standard strings
   • Not indexable in the same way for full-text search

3. Delimiter Length Counts
   Remember that delimiters (commas, pipes etc.) contribute to the total length:

   -- Results in 8001 bytes for 8001 characters
   STRING_AGG(CAST([col] AS nvarchar(MAX)), N','')

Alternative Approaches

Using FOR XML PATH (Before SQL Server 2017)

SELECT 
    c.id,
    c.bereichsname,
    STUFF((
        SELECT ',' + j.oberbereich
        FROM stellenangebote_archiv AS j
        WHERE j.bereich_id = c.id
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM bereiche c;

When to Use Each Method

Approach	Best For	Limitations
STRING_AGG with CAST	SQL Server 2017+, concise syntax	Requires explicit LOB casting
FOR XML PATH	SQL Server 2005+, complex transformations	Verbose syntax, needs encoding handling
CLR Aggregates	Extreme performance needs	Database CLR permissions required

Pro Tip

Always test aggregation length during development:

SELECT MAX(LEN(StringColumn)) * COUNT(*) AS EstimatedMaxBytes
FROM TableName
GROUP BY GroupColumn

By casting aggregated values to MAX length types, you safely bypass SQL Server's 8000-byte constraint for string aggregation output.