Concatenating varchar(max) columns into a string should work like varchar(n)
Currently, if you execute a statement like:
DECLARE @str varchar(max);
SELECT @str = '';
SELECT @str += [SomeColumn] + ',' FROM SomeTable WHERE someCondition=true ORDER BY someColumn;
The @str variable will contain all the requested values in the sting, PROVIDED the "SomeColumn" column is varchar(n). If it's varchar(max), then the value of @str will just be the LAST value of "someColumn".
This difference in behavior seems like a bug. The SELECT statement should really work the same way regardless of varchar(n) or varchar(max). In fact, if you add a CAST() to cast the varchar(max) column to varchar(n), suddenly it works as expected again.
I can give many examples, but this is proving to be a real problem for me, and trying to work around it is extremely painful.
Solomon Rutzky commented
Actually, I ran the example code posted in that Stack Overflow question and found that the @Dummy method does not work. And it's basically the same issue that you reported here: NVARCHAR(MAX) has the problem, but NVARCHAR(1 - 4000) does not. Sad.
Solomon Rutzky commented
Hi pbradshaw. It seems that "aggregate concatenation" (i.e. SELECT @x = @x + something) is not actually supported by SQL Server. The recommendation is to use one of the following:
1) FOR XML
Please see this answer on DBA.StackExchange for details: https://stackoverflow.com/a/15163136/577765
That being said, I think there is a minor change that you could make to what you are doing that might "fix" the unexpected optimization issue: add a dummy variable containing an empty string.
Given your example code:
DECLARE @str varchar(max) = '';
DECLARE @Dummy VARCHAR(1) = '';
SELECT @str += [SomeColumn] + @Dummy + ','
ORDER BY someColumn;
The goal here is to force it to evaluate that expression each time. Anyway, something to try, at least.
Also, just to put this out there, since SQLCLR is one of the official suggestions, and it works in versions starting at SQL Server 2005, I should mention that I included a SQLCLR aggregate function in my SQL# SQLCLR library ( https://SQLsharp.com/ ). It is called "Agg_JoinPlus", and is only available in the Full (i.e. paid) version, not in the Free version (which does include "Agg_Join" and "Agg_JoinDelim", but neither one handles ordering or duplicate removal). It works as follows:
tab.[col], -- Value NVARCHAR(4000)
N',', -- Delimiter NVARCHAR(4000)
tab.[col], -- OrderBy NVARCHAR(4000)
1, -- Ordering TINYINT: 0 = none, 1 = ASC, 2 = DESC
NULL, -- InitialDelimiter NVARCHAR(4000)
NULL, -- MinRecordsNeededForInitialDelimiter INT
NULL, -- FinalDelimiter NVARCHAR(4000)
NULL, -- MinRecordsNeededForFinalDelimiter INT
NULL, -- NullReplacement NVARCHAR(4000)
1, -- RemoveEmptyEntries BIT
1, -- DuplicateHandling TINYINT: 0 = keep, 1 = remove (i.e. DISTINCT)
1 -- Use Compression (to save on memory) BIT
FROM (VALUES ('a'), ('c'), ('b'), ('d'), (''), ('c')) tab(col);
Regarding STRING_AGG (mentioned by Steven in a previous comment):
1) it started in SQL Server 2017, hence not available in SQL Server 2016 or older
2) it DOES handle ordering
3) it does NOT handle duplicate removal ( https://feedback.azure.com/forums/908035-sql-server/suggestions/35243533-support-distinct-for-string-agg )
Take care, Solomon..
I can't use SQL2017's STRING_AGG function, because this code has to work on SQL2016.
This really needs to work this way. I consider it a bug that it doesn't.
Steven Hibble commented
I don't think this is supported behavior. You'll probably be pointed to SQL Server 2017's STRING_AGG function: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017