create table ap
(id int not null, 
 yx varchar(30)
 constraint pk_ap primary key (id)
)

insert into ap
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc' union all
select 4,'ddd' union all
select 5,'eee'

select * from ap

id          yx
----------- ------------------------------
1           aaa
2           bbb
3           ccc
4           ddd
5           eee



declare @r1 varchar(50),@r2 varchar(50)

select @r1='',@r2=''

select @r1=@r1+yx+',' from ap where id>=3
order by case when yx='yyy' then '1' else yx end

select @r2=''
select @r2=@r2+yx+',' from ap where id>=3
order by case when yx='yyy' then '1' else yx end
option(force order)

select @r1 r1, @r2 r2


r1                                                 r2
-------------------------------------------------- --------------------------------------------------
ccc,ddd,eee,                                       eee,

(1 row(s) affected)

declare @r1 varchar(50),@r2 varchar(50)

select @r1='',@r2=''

select @r1=@r1+yx+',' from ap where id>=3
order by 1,--yx  --case when yx='yyy' then '1' else yx end

select @r2=''
select @r2=@r2+yx+',' from ap where id>=3
order by 1,--yx --case when yx='yyy' then '1' else yx end
option(force order)

select @r1 r1, @r2 r2

------解决方案--------------------
option (force order)

FORCE ORDER

 指定在查询优化过程中保持由查询语法指示的联接顺序。

http://msdn.microsoft.com/zh-cn/library/ms181714%28v=SQL.90%29.aspx





------解决方案--------------------
看了下执行计划，第2个语句比第1个语句少了一次“计算标量”，难道问题就处在这里？

------解决方案--------------------
经测试，这个不是option(force order)造成的。以下两段的结果是一样的。
SQL code

declare @r1 varchar(50),@r2 varchar(50)

select @r1='',@r2=''

select @r1=@r1+yx+',' from ap where id>=3
order by 1

select @r2=''
select @r2=@r2+yx+',' from ap where id>=3
order by 1 
option(force order)

select @r1 r1, @r2 r2

------解决方案--------------------
查资料去，猜测应该和指定的排序有关系，验证中...

------解决方案--------------------
SQL code

declare @r1 varchar(12) set @r1=''
select @r1=@r1+yx+',' from ap where id>=3 order by yx
select @r1
/*
ccc,ddd,eee,
*/

declare @r1 varchar(12) set @r1=''
select @r1=@r1+yx+',' from ap where id>=3 order by yx option(force order)
select @r1
/*
ccc,ddd,eee,
*/

declare @r1 varchar(12) set @r1=''
select @r1=@r1+yx+',' from ap where id>=3 order by 1
select @r1
/*
eee,
*/

declare @r1 varchar(12) set @r1=''
select @r1=@r1+yx+',' from ap where id>=3 order by 1 option(force order)
select @r1
/*
eee,
*/

declare @r1 varchar(12) set @r1=''
select @r1=@r1+yx+',' from ap where id>=3 order by case when yx='yyy' then '1' else yx end
select @r1

/*
ccc,ddd,eee,
*/
declare @r1 varchar(12) set @r1=''
select @r1=@r1+yx+',' from ap where id>=3 order by case when yx='yyy' then '1' else yx end option(force order)
select @r1
/*
eee,
*/

------解决方案--------------------