title: Concatenating nvarchar(max) values doesn't seem to work (+= working as =)
body: <p>Using SQL Server 2012, I've found that trying to build up a string based on an nvarchar(max) column in a table doesn't seem to work correctly. It seems to overwrite, instead of append.  Arbitrary Example:</p>
  
  <pre><code>    DECLARE @sql nvarchar(max);
      SELECT @sql = N'';
      SELECT @sql += [definition] + N'
  GO
  '
      FROM sys.sql_modules 
      WHERE OBJECT_NAME(object_id) LIKE 'dt%'
      ORDER BY OBJECT_NAME(object_id);
  
      PRINT @sql;
  </code></pre>
  
  <p>This SHOULD print out all the SQL module definitions for all the various dt_ tables in the database, separated by GO, as a script that could then be run. However... this prints out only the LAST module definition, not the sum of all of them. It's behaving as if the "+=" were just an "=". </p>
  
  <p>If you change it just slightly... cast [definition] to an nvarchar(4000) for example, it suddenly works as expected. Also, if you choose any other column that is NOT an nvarchar(max) or varchar(max) type, it works as expected. Example:</p>
  
  <pre><code>DECLARE @sql nvarchar(max);
  SELECT @sql = N'';
  SELECT @sql += CAST([definition] AS nvarchar(4000)) + '
  GO
  '
  FROM sys.sql_modules 
  WHERE OBJECT_NAME(object_id) LIKE 'dt%'
  ORDER BY OBJECT_NAME(object_id);
  
  PRINT @sql;
  </code></pre>
  
  <p>Is this a known bug? Or is this working as expected? Am I doing something wrong? Is there any way for me to make this work correctly? I've tried a dozen different things, including ensuring every expression in the concatenation is the same nvarchar(max) type, including the string literal. </p>
  
  <p>NOTE: The example is just an example that shows the problem, and not exactly what I'm trying to do in real life. If your database doesn't have the "dt*" tables defined, you can change the WHERE clause to specify any group of tables or stored procedures in any database you want, you'll get the same result... only the last one shows up in the @sql string, as if you just did "=" instead of "+=". Also, explicitly stating "@sql = @sql + " behaves the same way... works correctly with every string type EXCEPT nvarchar(max) or varchar(max).</p>
  
  <p>I've verified that none of the [definition] values is NULL as well, so there are no NULL shenanigans going on.</p>