Posted by David Portas on 03/07/06 01:07

RickW wrote:
>
> I sort of expected this kind of response, but since it serves utterly no
> useful purpose whatsoever

I'm sorry you think that. Maybe you didn't read my reply too well. I
gave at least two practical suggestions: Use a proc; Improve your
standards.

One of the problems with SQL is that it doesn't fully support the
property of Relational Closure. In a true relational database we would
indeed expect every query to return a relation rather than an ordered
result set. However your statement about SQL queries returning tables
isn't quite correct. SELECT statements don't always return results that
qualify as a table even by SQL's definition. Neither SQL tables nor SQL
query results can always qualify as relations.

However, standard SQL and in general also SQL Server DO define the
results of queries as unsorted. The only time when ordering works is
when data is returned to the client because in that case the query
result is exposed as a cursor for row-bound operations at the client
side. It is the cursor-based result set that is ordered, not the query.
In well-designed systems it is usually the function of stored
procedures to return results to the client so that is where ORDER BY
clauses should typically be located.

There is perfectly good rationale why views should be unordered like
tables. I suggest you read a book on relational fundamentals for the
full explanations but meantime I'll just restate one obvious benefit
among many. Suppose you want to query the view in some way that filters
the results and returns only a small subset of the rows in the original
view. To force the view to sort the entire result before selecting from
it would be extremely inefficient, especially if you then wanted to
sort the query result in some different order. Since SQL Server has no
way to determine at creation time how a view will be used it follows
that views should be unsorted to support the most efficient query plan
in each case.

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