Concatenating strings in SQL Server, or undefined behaviour by design



The problem

So, given the following setup:

  Description NVARCHAR(100) NOT NULL,
  SortIndex INT NOT NULL

INSERT BadConcat 
  SELECT 1, 'First Item', 1 union all
  SELECT 2, 'Second Item', 2 union all
  SELECT 3, 'Third Item', 3


DECLARE @Summary NVARCHAR(100) = ''

SELECT @Summary = @Summary + ec.Description + ', '
FROM BadConcat ec
ORDER BY ec.SortIndex 

PRINT @Summary

And we get the following:

First Item, Second Item, Third Item, 


SET @Summary = ''

SELECT @Summary = @Summary + ec.Description + ', '
FROM BadConcat ec
WHERE ec.BadConcatID in (1,2,3)
ORDER BY ec.SortIndex 

PRINT @Summary

Now we get the following:

Third Item, 



The answer





And again, in response to another bug report:



The ONLY guaranteed mechanism are the following:




SELECT @Summary = (
  SELECT ec.Description + ', ' 
  FROM BadConcat ec 
  WHERE ec.BadConcatID in (1,2,3)
  ORDER BY ec.SortIndex 

PRINT @Summary


First Item, Second Item, Third Item, 

12 thoughts on “Concatenating strings in SQL Server, or undefined behaviour by design

  1. Yes, this is a much better way to do string concatenation in SQL. And then, to remove that pesky comma at the end of the string, we can use the STUFF command 🙂

    DECLARE @delim VARCHAR(10) = ‘, ‘

    SELECT @summary = STUFF(
    (SELECT @delim + ec.Description
    FROM BadConcat ec
    WHERE ec.BadConcatID IN (1, 2, 3)
    ORDER BY ec.SortIndex
    FOR XML PATH (”))
    , 1, DATALENGTH(@delim), ”)

    PRINT @summary﻿

  2. Just spent a few hours trying to figure out why my code was broken. Worked in one query but not the other. Thanks for writing this up. I wish M$ would support this feature. Everyone uses it. I’m surprised that it isn’t actually supported!

  3. Thanks for this Marc. Was brought to this article from your StackOverflow comment :

    Another commentator on this same thread mentioned that the += concatenation is considerably slower than XML Path … when the number of items to concatenate gets large (according to the commentator, James L., for 20,000 items: using the concatenate method – 2 minutes. Using the XML path method, it took less than 1 second

    However, if you have,
    a) a much smaller number of items to join than 20,000 and
    b) No where or order by clause

    Are there still cases where we can expect inexplicable behavior?

    1. Thanks for reading, Michele! Yes, by design the behavior is not specified, so updated query plans may cause changes to the concatenation behavior. This can also happen with updated versions of SQL Server.

      1. Thanks Marc.

        As you mentioned, apparently many people use this method, and appear to be able to use it successfully, and the fact that it is unreliable is just not understood.

        I think one problem is that we can follow an example of the concatenation method, such as your initial working example, and get the results you get.

        I suppose some people don’t spend a ton of time thinking of execution plans and/or are not well-versed in them, – and I admit I’m one of them. Aside from SQL Server upgrades, how can the “working” example of concatenation (with no where clause) have its plan changed, so that it will fail? What are common ways this kind of change might happen?

        – > Not just questions for Marc… I’d like to know if anyone has any ideas, or if anyone gets different results than Marc on the concatenation examples.

  4. The best information I had was in those MS articles linked in the blog post — and those have now disappeared into the Recycle Bin of so they are no longer accessible. Very sad. They do not even appear to be on the Wayback Machine.

    Microsoft also relocated all their blogs not that long ago and broke all those links, and some helpful content on this that was there is no longer indexed as far as I can tell.

    No good answers from me…

  5. Hello Again,

    Revisiting this:

    This is a little messy, but if you have SQL Server 2017 it looks like you can also USE STRING_AGG + hacking Group By (of course don’t use t.* in a db table that’s has lots of columns you don’t need. I’m just hacking the Group By in order to be able to Order By) :

    SELECT STRING_AGG(ec.Description,’,’) WITHIN GROUP (ORDER BY SortIndex) desc_list
    (select ‘fake_out’ as fake_col, t.*
    from BadConcat t ) ec
    WHERE ec.BadConcatID in (1,2,3)
    group by fake_col

    1. Okay – my bad.

      Turns out fake out not necessary, you can just do this:
      SELECT string_agg(ec.Description, ‘,’) WITHIN GROUP (ORDER BY ec.SortIndex)
      FROM BadConcat ec WHERE ec.BadConcatID in (1,2,3)

      (Thanks Erland Sommarskog, from MSDN fourms)

      1. Awesome, that’s much nicer for those of us who have managed to upgrade to 2017+ 🙂 (Do you have a link to the MSDN forum post?)

Leave a Reply
