|
|
Posted by David Portas on 03/22/06 01:56
Herb Caudill wrote:
> The fact of the matter is that SQL server used to work the way most
> naïve users would expect it to work - that is, views and functions with
> an ORDER BY clause returned ordered results. This worked 100% of the
> time in my experience
In that case your experience is very limited. It does not work anything
like 100% of the time and it never did.
> Even if it did, as RickW has pointed out, it makes no sense - even from
> a theoretical perspective - that SQL would lack an intrinsic object able
> to return ordered data. If a stored procedure can, then why can't a view
> or a function?
You didn't read this thread very well. A view or a function does NOT
return any data so ordering is not something that it can control. It is
queries against the view or function that return data and those queries
determine the order of the results returned. Those queries might reside
in a stored procedure or in client code but in EVERY case it is the
execution plan for the query that determines the order of rows
returned, not the view(s) that are referenced.
> issues you raise, but there are any number of ways that Microsoft could
> have pleased me *and* the massive-scalability crowd: whether with more
> intelligent and flexible optimization, or using a global switch, or a
> specifier that could be added to a view's definition - or simply by
> favoring performance if there is no ORDER BY clause present, and
> respecting the ORDER BY clause if it is there. As they say, it's a
> simple matter of programming. I think this is a rare instance of simple
> laziness on Microsoft's part.
It is not a simple matter at all. The problem is that this behaviour is
totally undefined. I suggest it would have been an almost
insurmountable challenge for Microsoft to reproduce every scenario on
SQL Server 2000, determine what the row order might be and then ensure
that order could be reproduced in 2005. Even if you limited the
requirement to a small number of possible scenarios I don't think there
would be any customer enthusiasm for time spent on such a thing at the
expense of other features. Of course you are welcome to suggest
potential new features for future versions.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|