Strange TSQL behavior with COALESCE when using Order By

Question

I'm having some very strange behavior with coalesce. When I don't specify a return amount (TOP (50)) I'm only getting a single last result, but if I remove the "Order By" it works... Examples below

DECLARE @result varchar(MAX)
SELECT @result = COALESCE(@result + ',', '') + [Title]
FROM Episodes WHERE [SeriesID] = '1480684' AND [Season] = '1' Order by [Episode] ASC
SELECT @result

Will only return a single last result:

The Shiva Bowl

But If I specifiy a max return amount (only adding TOP (50) to same statement)

DECLARE @result varchar(MAX)
SELECT TOP(50) @result = COALESCE(@result + ',', '') + [Title]
FROM Episodes WHERE [SeriesID] = '1480684' AND [Season] = '1' Order by [Episode] ASC
SELECT @result

I get all the results in proper order

The Draft,The Bounce Test,Sunday at Ruxin's,Mr. McGibblets,The Usual Bet,The Shiva Bowl

The culprit seems to be the [Title] column as if I return a different column it seems to be working without specifying a return limit. FYI [Title] is a VARCHAR(MAX) NOT NULL column.

Any insight what could be causing this? I really don't want to set a limit, but it's the only way it's returning all the data at the moment... Thanks

This is uncommented hack and you should not do like that. There are standart ways of doing this with xml. — Giorgi Nakeuri, Sep 27 '15 at 09:38
This is the reason we go for `XML Path()` to concatenate rows into single string — Pரதீப், Sep 27 '15 at 09:38
You can't rely that SQL Server will concatenate the first value, then the second, then the third one, and so on. It handles data on batches, so I believe that's why your case is not working. I suppose when you are using `TOP` clause and forcing ordering in such way, the engine is processing the values one by one. — gotqn, Sep 27 '15 at 09:42
@Sick I have answered the question in the past I think exactly what you want. This is a trick with exp log functions. Have a look http://stackoverflow.com/questions/29486877/how-to-calculate-running-multiplication/29487107#29487107 — Giorgi Nakeuri, Sep 27 '15 at 09:45
@GiorgiNakeuri I need to build a complex custom comma delimited string from the results, and that's why I'm not using the FOR XML output... — bfritz, Sep 27 '15 at 09:46
@bfritz, what do you mean? Can you show us that complex string? — Giorgi Nakeuri, Sep 27 '15 at 09:46
@GiorgiNakeuri - No here logic is quite different. I am the one who asked the question you referred — Pரதீப், Sep 27 '15 at 09:47

Lukasz Szozda · Accepted Answer · 2015-09-27T10:00:11.770

You cannot depend on concatenation like:

SELECT @result = COALESCE(@result + ',', '') + [Title]
FROM Episodes
...

Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

Example dependent on CTE/temp table/execution plan you will get different results:

SqlFiddleDemo

DECLARE @text VARCHAR(MAX) = ''
       ,@text2 VARCHAR(MAX) = '';

SELECT CAST(ROW_NUMBER() OVER (ORDER BY name) AS INT) AS number 
INTO #numbers 
FROM master..spt_values 


;WITH numbers (number)
AS
(
    SELECT CAST(ROW_NUMBER() OVER (ORDER BY name) AS INT) AS number
    FROM master..spt_values 
),a AS
(
    SELECT number FROM numbers WHERE number < 10
)
SELECT      @text = @text + LTRIM(STR(a.number))
FROM        a
ORDER BY    a.number DESC


;WITH numbers (number)
AS
(
    SELECT number FROM #numbers
),
a
AS
(
    SELECT number FROM numbers WHERE number < 10
)
SELECT      @text2 = @text2 + LTRIM(STR(a.number))
FROM        a
ORDER BY    a.number DESC

SELECT @text, @text2;

I made sample and your first query is working SqlFiddleDemo. But your solution is higly dependent on execution plan.

Use XML + STUFF for concatenation instead.

That SQLFiddleDemo shouldn't be working! That is exactly what I'm having this issue with, yet it's working there... — bfritz, Sep 27 '15 at 10:10
@bfritz My SQL fiddle has different structure/statistics/indexes and so on. But this is excelent example how it is dependent on internal representation and execution plan — Lukasz Szozda, Sep 27 '15 at 10:13
I may just have to stick with the TOP (50) hack to force the execution, I use this same method in a few other places already, and being my service handles over 80 million requests a day I try to steer away from solutions that have lots of nested selects etc due to server load. I just need to build a string (with a single select query) and return it back FAST! — bfritz, Sep 27 '15 at 10:26

Strange TSQL behavior with COALESCE when using Order By

1 Answers1

Linked