Sql-server – Why does the concatenation order change with a User-Defined Table Type

concatsql serversql server 2014

I've encountered what seems to me to be a strange ordering issue when using CONCAT with a User Defined Table Type.

I've put the SQL below that allows me to reproduce this:

I have the following user type:

CREATE TYPE [dbo].[StringList] AS TABLE
(
    [Value] VARCHAR(8000) NOT NULL
)

And the following query to set everything up:

DECLARE @ColumnNames AS TABLE([ColumnName] VARCHAR(MAX))
DECLARE @ValueTable AS [dbo].[StringList] --VariableLineA
--DECLARE @ValueTable AS TABLE([Value] VARCHAR(MAX)) --VariableLineB

INSERT INTO @ColumnNames([ColumnName])
VALUES
('Forename'),
('Surname')

INSERT INTO @ValueTable([Value])
VALUES
('Ellis'), ('Ali')

And this is the query I'm running:

SELECT CONCAT(STRING_AGG(CONCAT([ColumnName], ' - ',  [sc].[Value]), ', '), ', ') -- QueryLineA
        --CONCAT(STRING_AGG([ColumnName] + ' - ' + [sc].[Value], ', '), ', ') --QueryLineB
FROM        @ValueTable AS [sc]
INNER JOIN  @ColumnNames ON 1=1

If the VariableLineB line is uncommented, and the VariableLineA line is commented out, the following string is returned for both query lines if run individually or together:

Forename - Ellis, Forename - Ali, Surname - Ellis, Surname - Ali,

If the VariableLineA line is uncommented and the VariableLineB line is commented out, the following string is returned for QueryLineB, and from both queries if run together, but not QueryLineA on its own:

Forename - Ellis, Forename - Ali, Surname - Ellis, Surname - Ali,

When QueryLineA is run on its own, the following string is returned:

Forename - Ellis, Surname - Ellis, Forename - Ali, Surname - Ali,

Why has the order changed? What am I seeing here, and is there any way to prevent this from happening?

In terms of usage, the values of @ValueTable would be getting passed into a function, and it's important that the original order (whatever that may be) remains the same.

Best Answer

The order changes because the order of the tables involved in the cross join has changed. In one plan @ValueTable is the outer (driving) table, in the other @ColumnNames is the outer table.

for each row in @ColumnNames
    for each row in @ValueTable
    concatenate the result
    //produces Forename - Ellis, Forename - Ali, Surname - Ellis, Surname - Ali, 


for each row in @ValueTable
    for each row in @ColumnNames
    concatenate the result 
    //produces "Forename - Ellis, Surname - Ellis, Forename - Ali, Surname - Ali,"

Order is never guaranteed unless specified in the query, so if consistent order is important you should add a stable order by clause defining order for both tables inside the STRING_AGG definition.

SELECT CONCAT(STRING_AGG(CONCAT([ColumnName], ' - ',  [sc].[Value]), ', ') 
                        WITHIN GROUP (ORDER BY [ColumnName],  [sc].[Value])  , ', ') -- QueryLineA
FROM        @ValueTable AS [sc]
INNER JOIN  @ColumnNames ON 1=1
Related Question