Posted by David Portas on 03/23/06 09:43

RickW wrote:
> By the way, across 8 years of SQL Server 7/2000 use, I never had an
> instance of using ORDER BY in a view that failed to return an ordered
> record set.
>
> Obviously I need someone to explain why, if it's okay to use ORDER BY in
> a query to get an ordered data set, it's not okay to store that query as
> a view in a tool like SQL Server and see that ordered result in my
> application, without having to write a query from the application.

This has been answered several times now. It is the QUERY that you make
against the view that determines the order of the result, not the view
itself. Views cannot possibly "fail to return an ordered record set"
because views don't return anything at all. The only way to return data
from a view is to query the view:

SELECT ...
FROM your_view
WHERE ... etc ;

If that query has an ORDER BY then the result is sorted accordingly. If
it doesn't then the order is undefined. This has been the case in all
versions of SQL Server.

You mentioned that you are using Access as a front end. If you linked
the views in an Access database then Access will generate a query for
you. I don't know whether Access gives you the option to specify the
order or not. Perhaps it doesn't or maybe that option is poorly
implemented or maybe you just didn't know it was there. I expect Access
just does "SELECT * FROM your_view ;" by default. In any case, SQL
Server will do exactly what is specified by the query generated by
Access.

As I tried to show with the example I posted in reply to Serge, it
isn't always possible to define a sensible "natural" order for a query
against a view. That's why your expectations that queries against views
should always be sorted are unreasonable unless you want to disallow
certain types of queries.

If you want to propose a new feature that views should specify sorts
for *sone* queries then someone will have to create a whole
specification for which queries are sorted, which aren't and what
errors or warnings are generated for the ones that aren't. The
consequences for the user or developer are terrible. Either the
developer has to cope with his old queries now generating error
messages (even if he didn't want them ordered). Or alternatively, you
don't issue any error messages for queries that can't be sorted and the
developer has to read the documentation to work out which ones will be
sorted and which won't (in other words RTFM - no different to the
situation we have today in fact!)

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

[Back to original message]