string_agg() does not max alone — #SQLServer #SQLNewBlogger

… and that surely is for good reason when you check how the nvarchar(max)/varchar(max) family is treated.

---

As I moved from PostgreSQL to SQL Server 2008R2 one of the things I missed the most were all the out of the box aggregation functions. And one of them was string_agg().

We migrated last year from 2008R2 to 2017 and I was more than happy to get the function back in my toolkit. Or so I though.

A part of our major ETL process is based on cursor. And I knew the string_agg could help us in simplifying and reducing the procedure definition. So here I go happily and …

Msg 9829, Level 16, State 1, Line 3

STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation

Your dear SSMS 🙂

What a disappointment. In PostgreSQL I never reached such a restriction. I looked for solutions but was kind of left with no success.

Recently I gave it a try again. For so called ICDO codes, we get a main text, and many synonyms. I wanted to get the many synonyms in the last column for some report. And again… the Msg 9829.

However this time the ‘LOB’ caught my eyes. I had been looking for the impact of NVARCHAR(max) on storage and performance, and I could now make the relation:

string_agg(cast(synonym to nvarchar(max)))

Still waiting for GREATEST()/LEAST() function, but we have at least (pun intended) one funky solution (not a fan of the STUFF or PIVOT variants)

select 
    (select max(tomax)
     from (values (col1),
                  (col2),
                  (col3))
         as x(tomax)) as greatest
from yourtable

Reconsider your assumption and have a nice day!

---

drop table if exists #sometext;
with t1 as (select * from (values ('some'), ('text')) as x(txt)),
    t2 as (select x.txt from t1 join t1 as x on 1=1),
    t3 as (select x.txt from t2 join t2 as x on 1=1),
    t4 as (select x.txt from t3 join t3 as x on 1=1),
    t5 as (select x.txt from t4 join t4 as x on 1=1)
select cast(txt as varchar(200)) as txt
into #sometext
from t5;
print 'let''s fail';
select STRING_AGG(txt, ', ') from #sometext; -- let's fail
print 'let''s go';
select STRING_AGG(cast(txt as varchar(max)), ', ') from #sometext; -- let's go