0

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?????

Community
  • 1
  • 1
Anton
  • 48
  • 5
  • Impossible to say without knowing how many tables are in there. It's far more likely that you only have a single table in the `dbo` schema. or the statements are wrong.BTW why not query `sys.tables` ? Eg `select * from sys.tables where schema_id in (SCHEMA_ID('dbo'),SCHEMA_ID('data'))` – Panagiotis Kanavos Apr 03 '18 at 13:25
  • @PanagiotisKanavos take a look on "rows affected". It's same number of tables (actually, this is the same database). This sample is just part of another query, so it can be any FROM table(s). – Anton Apr 03 '18 at 13:30
  • @PanagiotisKanavos hm, but actually you partially right, same query with sys.tables works fine. (just did change sys.objects to sys.tables) But know I'm even more confused. – Anton Apr 03 '18 at 13:45
  • You are searching for *objects*, not tables in schema `data`. Since you want to execute `delete from table` it doesn't make sense to retrieve functions, stored procedures etc. – Panagiotis Kanavos Apr 03 '18 at 13:55
  • Anyway, what do you want to do? If you want to deploy a database or schema to SQL Azure or any other server, it's better to use eg a `dacpac` or a script instead of copying/restoring a database and clearing the data. If you want to clear all tables eg in a staging schema or filegroup, you can retrieve the name and schema with a cursor and execute eg `exec ('truncate table ' + @schema + '.' + @name')` with values retrieved from the cursor. – Panagiotis Kanavos Apr 03 '18 at 13:57
  • @PanagiotisKanavos Actually, I want to **understand** why in this particular case SELECT with CONCAT doesn't work as expected and why behavior on different platform is not the same :) – Anton Apr 03 '18 at 14:06
  • CONCAT doesn't affect the number of results, only how they look. It just concatenates three strings. If you get fewer rows than you expected, it's because fewer rows match the query expression – Panagiotis Kanavos Apr 03 '18 at 14:11
  • BTW *print* isn't meant to return results. It's meant for informational messages. The text returned by PRINT will only be sent to the client once a message buffer is full. *Clients* can't access PRINT messages through query results, only as messages sent by the connection. – Panagiotis Kanavos Apr 03 '18 at 14:13
  • 1
    And finally, CONCAT isn't STRING_AGG or GROUP_CONCAT. You can't use it to concatenate results, it simply concatenates a bunch of strings. If you want to aggregate strings, use `STRING_AGG` on SQL Server 2017 or one of the string aggregation techniques for previous versions, eg using `FOR XML PATH`. – Panagiotis Kanavos Apr 03 '18 at 14:15

1 Answers1

1

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.

Razvan Socol
  • 5,426
  • 2
  • 20
  • 32