3

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.

9

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
| improve this answer | |
  • 1
    Very informative, thanks! This may be a dumb question, but does this apply to every result without an ORDER BY clause? I was aware that results are 'unordered' when querying from normal tables, but I guess I somewhat naively didn't realise this could also apply to the likes of STRING_SPLIT (and potententially everything else)? – Interminable Dec 21 '19 at 9:25
  • 3
    yes, this is always the case. The documentation for string split explicitly warns The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string but unfortunately they don't provide any alternative output column in the result of the function indicating the order so this is often ignored. – Martin Smith Dec 21 '19 at 11:46
3

In SQL Server, the order of the returned rows is not guaranteed unless you use an ORDER BY clause in your query, since a table is by definition an unordered set of rows. Thus, the results you add to another table (or table variable) won't have a specific row order unless you give it one when you do a select.

Since you need @ValueTable to have a specified order, you will need to add a sequence to the table - an IDENTITY column will work - and ORDER BY in your joined query, something like this:

CREATE TYPE [dbo].[StringList] AS TABLE
(
    [Order] Int Identity(1,1) not null,
    [Value] VARCHAR(8000) NOT NULL
)
| improve this answer | |
2

STRING_AGG() function may contain optional ordering WITHIN GROUP clause which specifies the ordering of separate values in aggregated result.

When ordering clause is NOT specified then the values ordering in aggregated value is NOT specified. It is RANDOM and non-deterministic (it may differ from query execution to the next one).

In most cases it is defined by what object was scanned for to extract separate values for concatenation - table scan, index (clustered or non-) scan, temporary table/index, extracting by referenced field value while extracting values from another fields of joined table using this another table scan by some way, etc...

Query plan change or/and any another factor may change extracting order (even when the plan stays unchanged!), and this cause that the aggregation order may change too.

Remember - "no values ordering clause specified or documented" is equal to "the ordering may be any" in all cases.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.