String concatenation gotcha

In SQL Server, there are a few ways to concatenate string values from a column in a table, e.g. using FOR XML TYPE method. The following example is another method we all use:

USE tempdb;

GO

CREATE TABLE MyTestTable (id int not null identity, col1 nvarchar(30) NOT NULL, col2 nvarchar(20) not null);
INSERT MyTestTable(col1,col2) VALUES (‘aaa’,’aa’),(‘bbbb’,’bb’),(‘cccc’,’cc’),(‘dddd’,’dd’);
GO

DECLARE @str nvarchar(max);
SET @str=”;
SELECT @str=@str+col1+’,’ FROM MyTestTable ORDER BY id;
SELECT @str;

SET @str=”;
SELECT @str=@str+col1+’,’ FROM MyTestTable ORDER BY col1;
SELECT @str;
GO

— We take this as granted. But does it always work?

— It does NOT work if ordered by a computed column in the subquery
DECLARE @str nvarchar(max);
SET @str=”;
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, col1+CAST(id as nvarchar(20)) AS tmpcol FROM MyTestTable) t ORDER BY t.tmpcol;
SELECT @str;
SET @str=”;
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, col1+’_’+col2 AS tmpcol FROM MyTestTable) t ORDER BY tmpcol;
SELECT @str;
GO

— It does NOT work if ordered by a computed column in the subquery
— even just data type change

DECLARE @str nvarchar(max);
SET @str=”;
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, CAST(id as nvarchar(20)) AS tmpcol FROM MyTestTable) t ORDER BY tmpcol;
SELECT @str;
SET @str=”;
— even value not changed at all
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, col1+” AS tmpcol FROM MyTestTable) t ORDER BY tmpcol;
SELECT @str;
GO
— It works if ordered by a physical column in the subquery
DECLARE @str nvarchar(max);
SET @str=”;
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, col1+CAST(id as nvarchar(20)) AS tmpcol FROM MyTestTable) t ORDER BY col2;
SELECT @str;
GO

— It is the same result if change the table MyTestTable to have PK on any column.
— Tested on SQL Server 2008R2 RTM Developer Edition