Re: ORDER BY in VIEW not working

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
--

.

Relevant Pages

  • Re: ORDER BY in VIEW not working
    ... Only the queries issued against a view can ... typing an ORDER BY clause. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Views
    ... occurs with even simple queries. ... If you can't reproduce it with a limited set of test data but can do so ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.clients)
  • Re: Joining tables in Access
    ... As a matter of fact if I don't add the parentheses in the FROM clause of a ... multi-table query, the query fails with a syntax error. ... SQL view to enter your queries. ... Or use the query grid just like you would in SQL Server (although the ...
    (microsoft.public.access.queries)
  • Re: Listing objects (tables and queries) within a query
    ... I have a number of queries that have a problem with a join ... local MS-Access table and the other is a linked SQL Server db table. ... I need to retrieve the matched records somehow. ... the tables in the from clause and any additional fields in the where, ...
    (microsoft.public.access.modulesdaovba)
  • Collation
    ... clause in queries ... Right now it has default SQL Server collation SQL_Latin1_General_Cp1_CI_AS ...
    (microsoft.public.sqlserver.server)
Loading