Resolution for concatenate column values issues

I would like to add a note before you go ahead and read this article. Please do not take this as an approach guaranteed to work. I have come across instances when the solution I suggested didn't work. Please read through this, purely from an academic perspective, and do not, I repeat, do not incorporate these suggestions in your production environment.

I am still keeping the article as I had originally written since I would like to maintain the arguements and the counter arguements.

Please read through the comments (given by Hugo Kornelis), to understand the complete picture. I would like to thank Hugo for his valuable inputs.

Finally, I would like to stress on the one point I had written in the article "Absence of proof is not proof of absence" :) Just because you don't see a reason for it to fail doesn't mean it won't.

On the lighter side, this reminds me of the conversation between Henry Wu and Ian Malcolm in Jurassic Park :)

"Henry Wu: You're implying that a group composed entirely of female animals will... breed?
Ian Malcolm: No, I'm simply saying that life, uh... finds a way. "

Feel free to give your comments. Happy Reading.
-Omni
-----------------------------------------------------------------------------------------------------

I am writing this post based on the a few issues pointed out by Anith Sen for using an approach for concatenating the column values (in SQL Server 2000). The approach that I suggested (in question) is given here.

Concatenate the values in a column in SQL Server 2000 and 2005

The examples, that I am going to use to explain on how to resolve the issue, are actually given, by Anith Sen and Umachander (Thanks to them), as issues with the "set based concatenation", in the SQL Server Discussion Forum.

Before I start it off, I want make clear that I am attempting to explain why are there issues in concatenation and what I think might be a solution to overcome the issues. Nevertheless, whether the approach I suggest works or not depends entirely on the query execution plan that you get for the query. And maybe after reading this if you cannot make it work, you will atleast know why it doesn't work :)

I am just trying to figure out why do we don't get the results as expected and maybe suggest a work around. Waiting for the experts inputs on this though :)

To start with, lets take the following query. It works fine..

declare @au_id varchar( 8000 )
set @au_id = ''
select @au_id = @au_id + a.au_id + ','
from pubs..authors as a
order by a.au_id
print @au_id

But, this doesn't.

declare @au_id varchar( 8000 )
set @au_id = ''
select @au_id = @au_id + a.au_id + ','
from pubs..authors as a
order by a.au_id + ','
print @au_id

If I analyze the query execution plan, i see that there is an index scan, followed by compute scalar, followed by a sort and then a select (from right to left, as always). Here the problem is, compute scalar computes 2 expressions. One for the value that is selected and assigned and one for the order by (appending a comma to the column and then use it for sorting). Since the order by expression has to be computed before the ordering, SQL Server brings in the other computation here (work of the optimizer :) and so @au_id + a.au_id + ',' is computed for all the rows with the initial value of @au_id( as ''). Since the sort happens after the computation, the value for @au_id is re-assigned for every row and will take the au_id of the last row (its the max here cos of the order).

Well, for this concatenation to work, what we need is just this:
In the query execution plan the select should happen immediately after the computation for the concatenation expression.

There are a few ways to do it.
1. Don't order by any column (Best of all)
2. No computation other than the appending should happen in the query. if you are ordering by a computed column (or getting the distinct column values), use that computation within an inline view.

Well, I would suggest the thumb rule is:
When you write your query, execute and check the execution plan, the last operation will be a select. Make sure the penultimate operation is the compute scalar (which does only the concatenation operation and nothing else), and if any other operation gets between these two, then your result is screwed.

I guess thats the bottom line. So to make the above query work. I would do something like this, to force the two computations to be split and bring the concatenation just before the select.

declare @au_id varchar( 8000 )
set @au_id = ''
select @au_id = @au_id + a.au_id + ','
from (select top 100 percent au_id, au_id + ',' as ord from pubs..authors
order by au_id + ',') as a
order by ord
print @au_id

Lets take the other examples. Check the query execution plans for the following as see when the compute scalar operation is with respect to the select.

This one doesn't work (why? check the QEP)..

DECLARE @Str VARCHAR(8000)
SET @Str = SPACE(0)
SELECT @Str = @Str + CustomerID + ','
FROM NorthWind..Orders
ORDER BY RIGHT(ShipAddress, 2)
PRINT @Str

Well, I can change it this way and it works (Check the QEP now)

DECLARE @Str VARCHAR(8000)
SET @Str = SPACE(0)
SELECT @Str = @Str + CustomerID + ','
FROM (select top 100 percent *,RIGHT(ShipAddress, 2) as ord from NorthWind..Orders
ORDER BY RIGHT(ShipAddress, 2)) as a
order by ord
PRINT @Str

The above strategy can be applied to all the queries that has an issue in the ordering or distinct. Here is an example for distinct, for it to work you may use it this way...

DECLARE @Str VARCHAR(8000)
SET @Str = SPACE(0)
SELECT @Str = @Str + c1.column_name from (select DISTINCT column_name
FROM pubs.INFORMATION_SCHEMA.COLUMNS where table_name = 'authors') AS c1
PRINT @Str

Can we be sure that this will work? There is nothing I know of now that will fail. Again, as someone said, absence of proof is not proof of absence :) I will just wait for the special cases, which is bound to come from someone.
But, more than the approach its the QEP that matters. I am sure it will work if we get the QEP as I had suggested.
Let me know if anybody thinks there is an issue with this approach. Let sort this once and for all.. its been a long time :)