加了个hint,为何结果不一样,求解释.

Question

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 apid          yx
----------- ------------------------------
1           aaa
2           bbb
3           ccc
4           ddd
5           eeedeclare @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 endselect @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)

Answer 1

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)问题在红色这里。

Answer 2

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 endselect @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
你都改成order by 1 或是 order by yx 然后看一下结果，应该就相同了。

Answer 3

option (force order)FORCE ORDER  指定在查询优化过程中保持由查询语法指示的联接顺序。http://msdn.microsoft.com/zh-cn/library/ms181714%28v=SQL.90%29.aspx

Answer 4

经测试，这个不是option(force order)造成的。以下两段的结果是一样的。declare @r1 varchar(50),@r2 varchar(50)select @r1='',@r2=''select @r1=@r1+yx+',' from ap where id>=3
order by 1select @r2=''
select @r2=@r2+yx+',' from ap where id>=3
order by 1 
option(force order)select @r1 r1, @r2 r2

Answer 5

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,
*/

Answer 6

option(force order)的作用不太清楚，官方说明是质疑使用指定连接方式，但这里明显不会起作用。但加入了这个语句后，优化器把计算字符串的工作放到了第一个标题计算迭代器里，应该是因为有这个语句的存在所以符合了它认为可以直接输出的条件，所以加了以后，实际并没有在计算好的yx+','的结果集里进行累加，而是在开始的计算case when yx='yyy' then '1' else yx end的迭代器里就把字符串计算好了，而且只记录了最后一行而非连接所有。SELECT @STR=@STR+这种语法应该不是官方支持的字符串累加方式，只是使用者自己找到的方法，所以才会出现不同查询中结果不同。官方推荐的应该还是CLR聚合函数。所以这种问题去官网找答案估计也找不到。

Answer 7

不能说是BUG,这种拼接方式本来就不是官方支持的，只能算小甜点。

Answer 8

-- 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'
-- 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 endselect @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                                                 
-------------------------------------------------- -------------------------------------------------- 
eee,                                               eee,2K的表示结果一致

Answer 9

-- 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'
-- declare @r1 varchar(50),@r2 varchar(50)select @r1='',@r2=''select @r1=@r1+yx+',' from ap where id>=3
select @r2=@r2+yx+',' from ap where id>=3 order by yx
select @r1 r1, @r2 r2
/*r1                                                 r2                                                 
-------------------------------------------------- -------------------------------------------------- 
ccc,ddd,eee,                                       eee,（所影响的行数为 1 行）但你的跟这个的结果却相同的。这是加ORDER BY 与没加ORDER BY 累加字符差别。

Answer 10

应该是 force order的bug，我看了一下执行计划，的确不一样

Answer 11

去掉这个option(force order)执行结果就是一样的了。因为这个选项影响了执行计划。
由于 SQL Server 查询优化器通常会为查询选择最佳执行计划，因此我们建议资深开发人员和数据库管理员只有在不得已时才可使用提示。

Answer 12

option(force order)
不是蛮理解 ..