… 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 truncationYour 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