title: Azure T-SQL SELECT concatenation behavior
body: <p>I have really strange problem (or misunderstanding) of the string concatenation on Azure Sql. I used to build T-SQL code for EXEC like this:</p>
  <pre><code>DECLARE @tsql nvarchar(max)
  SELECT @tsql = CONCAT(@tsql, Prop1, Prop2) FROM Table
  PRINT @tsql
  </code></pre>
  <p>This works fine in SQL from 2012 to 2016. But in Azure SQL I have really strange behavior: only LAST row is in @tsql</p>
  <p>Here is live T-SQL to clean up some tables:</p>
  <pre><code>    DECLARE @deleteTsql nvarchar(max)
      SET @deleteTsql = ''
      -- just show all table
      SELECT O.[name] FROM [sys].[objects] O
      LEFT JOIN sys.schemas SCH on O.schema_id = SCH.schema_id
      WHERE (SCH.[name] = 'dbo' AND [type] = 'U') or (SCH.[name] = 'data' and O.[name] = 'Objects')
      ORDER BY O.create_date desc
  
      -- build T-SQL to cleanup
      SELECT @deleteTsql = CONCAT(@deleteTsql, 'PRINT ''Deleting data from ', O.[name], '''', CHAR(10), CHAR(13), 'DELETE FROM [', SCH.[name], '].', '[', O.[name],']', CHAR(10), CHAR(13)) FROM [sys].[objects] O
      LEFT JOIN [sys].[schemas] SCH on O.[schema_id] = SCH.[schema_id]
      WHERE (SCH.[name] = 'dbo' AND [type] = 'U') or (SCH.[name] = 'data' and O.[name] = 'Objects')
      ORDER BY O.create_date desc
      
      PRINT @deleteTsql
  </code></pre>
  <p>This works fine in SQL 2016. For instance I have tables data.Objects, dbo.Table1 and dbo.Table2. Then following will be printed:</p>
  <blockquote>
  <p>(3 rows affected)</p>
  <p>PRINT 'Deleting data from Objects'</p>
  <p>DELETE FROM [data].[Objects]</p>
  <p>PRINT 'Deleting data from Table1'</p>
  <p>DELETE FROM [dbo].[Table1]</p>
  <p>PRINT 'Deleting data from Table2'</p>
  <p>DELETE FROM [dbo].[Table2]</p>
  </blockquote>
  <p>But if I do the same on Azure SQL (v12) database with same tables then I got this:</p>
  <blockquote>
  <p>(3 rows affected)</p>
  <p>PRINT 'Deleting data from Table2'</p>
  <p>DELETE FROM [dbo].[Table2]</p>
  </blockquote>
  <p>So only last record is in @deleteTsql.
  Why?????</p>