Known bug or is there a reason?

Question

Known bug or is there a reason?

john-132685

SSCertifiable

Points: 6134
More actions
October 19, 2004 at 11:49 am

#65697

So the question really is, is this a known bug or is this by design? (and if by design can you explain?)
That computed sorts don't work with concatenation
declare @a varchar(1000)
select @a = ISNULL( @a, '' ) + CONVERT(VARCHAR(10),id ) + ','
FROM sysobjects
ORDER BY id %10
SELECT @a
GO
declare @a varchar(1000)
select @a = ISNULL( @a, '' ) + CONVERT(VARCHAR(10),id ) + ','
FROM (
SELECT TOP 100 PERCENT id
FROM sysobjects
ORDER BY id %10
) w
SELECT @a

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I think in this particular instance that it is by design. The reason I think this is you are performing a computed calculation but to be able to perform that the server needs to know the data to apply the calculation to.

The example you provide pulls back the sysobjects table in memory (or cache or any other term that you feel comfortable with) and then the server knows here is the data to work with. The other way the server says "I know you want to use x but out of x what data do you want?"

I may be wrong or over simplifying things here and I am sure that any of the great SQL gurus that are here can point us both in the right direction.

The above is just what I feel happens based off the result that I see from running the code.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

john-132685 SSCertifiable Points: 6134 More actions · Answer 2

Yes, but:

declare @a varchar(1000)

select @a = ISNULL( @a, '' ) + CONVERT(VARCHAR(10),id ) + ','

FROM sysobjects

ORDER BY id

SELECT @a

Works. That is, with no calc in the order by it concatenates all rows.

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 3

It's a known problem:

http://support.microsoft.com/?kbid=287515

Indeed, aggregate concatenation doesn't work with order by an expression.

The first workarouds that I've tried do not work:

- subqueries

- views

I have found only two working workarounds:

- a computed column in the base table

- temporary tables or table variables

Razvan

PS. Anyway, aggregate concatenation itself is not ANSI SQL and is not documented as a feature of MS SQL, either (as far as I know). So it's just a feature that works... by chance. I have to recognize that I also use it, but I try to use it only on base tables (because of this problem).

dongadoy SSC Eights! Points: 880 More actions · Answer 4

I've also noticed that an ORDER BY list with many columns also causes aggregate concatenation to be unordered. When I reduced the number of columns in the ORDER BY, aggregate concatenation order worked fine.

john-132685 SSCertifiable Points: 6134 More actions · Answer 5

1) In Standard SQL, which you should be trying to write if you are a professional programmer, the ORDER BY clause is part of a cursor and can only have the column names from the SELECT list in it. Think about the SQL model and nothing else makes sense.

Which standard are you referring to? What about the original one that never had cursors because row at a time processing is not part of relational theory?

It was the bozos at microsoft that added the requirement to use order by when one (by definition) could rely on the clustered index for most uses. So now they don't want to support it?

As for cursors... you must get paid more the longer it takes your code to execute.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

The requirement to state 'order by' if you want to guarantee a certain order in the result has always been there - it has not been added by MS.

Only bozos who knew that the former SQL Server implementation(s) had a 'feature' that most of the times allowed you to get away without using 'order by' did so.

How rows are physically stored has nothing to do with in which order rows found by a query is returned.... unless you say ORDER BY

It's always been this way, this is nothing new....

/Kenneth

Antares686 SSC Guru Points: 125444 More actions · Answer 7

How rows are physically stored has nothing to do with in which order rows found by a query is returned.... unless you say ORDER BY

Actually this has always been untrue, the fact is the storage order does affect the order of output but so do non-clustered indexes if one is used, choice of cursor thru index structure, and a lot of other conditions including things that happen in the cache factor into how the data is returned. ORDER BY is the only way to ensure you get the expected sort for the data.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Apologies for being unclear, my mind was in 'generic' SQL mode, not SQL Server specific

My intention was to say;

How rows are physically stored does not necessarily guarantee in which order rows found by a query is returned .... unless you say ORDER BY

The fact that many skips 'order by' is in itself proof that physical structures indeed influences the 'common' resultset - that wasn't my intended message.

If 'order by' isn't specified, rows will be returned in the order they are retrieved - this order may or may not be the same time after another. The only way to ensure expected ordering is to use 'order by' - nothing else. And this is certainly nothing new in SQL.

/Kenneth

Antares686 SSC Guru Points: 125444 More actions · Answer 9

Thanks for your change and hope I didn't come off rude.