|
|
Posted by vicky on 01/11/07 09:46
Hi Plamen and Erland,
I believe and agree with the wording-- The correct behavior for an
aggregate concatenation query is undefined. at
http://support.microsoft.com/default.aspx/kb/287515 and query processor
builds different execution plan when expressions are applied to columns
in the query's ORDER BY clause....
Here is another solution(removes overhead of order by clause) to this
which I m thinking of
same TEST table I have taken for this
CREATE TABLE Test (
col1 INT,
col2 VARCHAR(max))
INSERT Test (col1, col2) VALUES(1, '1.1')
INSERT Test (col1, col2) VALUES(1, '1.2')
INSERT Test (col1, col2) VALUES(2, '2.1')
INSERT Test (col1, col2) VALUES(2, '2.2')
INSERT Test (col1, col2) VALUES(2, '2.3')
CREATE TABLE #tst (
col1 INT,
col2 VARCHAR(max))
insert into #tst
select distinct(col1),'''' as col2
from test
update t
set col2 = (select stuff((select ' | '+ col2 as 'data()'
from Test t1
where t1.col1 = t.col1
for xml path ('')),1,2,''))
from #tst t
select col1,col2
from #tst
would produce expected result :>
col1 col2
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3
Thanks and regards,
Vikas
Database Developer
Plamen Ratchev wrote:
> Yes, the wording is not very clear. I trusted the statement in the
> workaround section as well as the samples provided below.
>
> Thanks!
>
> Plamen Ratchev
> http://www.SQLStudio.com
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns98B3675065125Yazorman@127.0.0.1...
> > Plamen Ratchev (Plamen@SQLStudio.com) writes:
> >> I believe you are referring to the behavior explained in this KB article:
> >> http://support.microsoft.com/default.aspx/kb/287515
> >>
> >> The way this query is written it follows exactly the workaround solution
> >> explained in the article (to apply any function or expression to the
> >> SELECT list columns rather than in the ORDER BY clause). According to
> >> the article then the query will achieve the expected results.
> >
> > But observe the first paragraph under CAUSE. The article then bends over
> > backwards to do it anyway, which contradicts the first paragraph. I prefer
> > to trust that first paragraph that says the correct behvaiour is
> > undefined.
> >
> >> This query was tested with both SQL Server 2000 and SQL Server 2005.
> >
> > Yes, but did you get expected results, because you should get them, or
> > was it mere chance? Compare the TOP 100 PERCENT .. ORDER BY in views that
> > many incorrectly relied on in SQL 2000, and then were bitten by in
> > SQL 2005.
> >
> >
> >
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> >
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|