CODEWITHSUNDEEP
sql-servert-sqlazure-sql-database
Anton
Anton

Reputation: 48

Azure T-SQL SELECT concatenation behavior

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:

DECLARE @tsql nvarchar(max)
SELECT @tsql = CONCAT(@tsql, Prop1, Prop2) FROM Table
PRINT @tsql

This works fine in SQL from 2012 to 2016. But in Azure SQL I have really strange behavior: only LAST row is in @tsql

Here is live T-SQL to clean up some tables:

    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

This works fine in SQL 2016. For instance I have tables data.Objects, dbo.Table1 and dbo.Table2. Then following will be printed:

(3 rows affected)

PRINT 'Deleting data from Objects'

DELETE FROM [data].[Objects]

PRINT 'Deleting data from Table1'

DELETE FROM [dbo].[Table1]

PRINT 'Deleting data from Table2'

DELETE FROM [dbo].[Table2]

But if I do the same on Azure SQL (v12) database with same tables then I got this:

(3 rows affected)

PRINT 'Deleting data from Table2'

DELETE FROM [dbo].[Table2]

So only last record is in @deleteTsql. Why?????

Upvotes: 0

Views: 189

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5694

The result for aggregate concatenation (with the SELECT @x=@x+... syntax) is undefined. See KB 287515 and this answer. Using CONCAT does not change that. As Panagiotis Kanavos suggested, you should use STRING_AGG or GROUP_CONCAT.

And one more thing: in my databases, I get the results from a single row using your query, even in SQL Server 2016 or SQL Server 2017. So it's not an Azure thing, it's just depending on the execution plan (if it is using a nested loops join, you get all the rows; if it is using a merge join, you get a single row). That may be dependant on the number of rows in the system tables, the CPU count, the updated statistics, etc.

Upvotes: 1

Related Questions