ORDER BY in VIEW not working

[Beowulf]

I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don't come through
sorted by Numb.

Everything I've read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an idea
why the sorting is not working correctly for this particular view? thanks.

CREATE VIEW vwRouteReference
AS
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,
tblItem.Numb, tblQuestion.DescrPrimary AS Type
FROM tblItem INNER JOIN tblQuestion
ON (tblItem.ID = tblQuestion.Item_ID)
WHERE (((tblItem.Category_ID)>0))
UNION
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS FullName,
tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS Type
FROM tblItem
WHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR
(tblItem.Type) = 'Route'))
ORDER BY tblItem.Numb

[David Portas]

RickW wrote:

I'm a
bit ticked that a very useful feature, in place for almost a decade, was
taken away without more reasonable notice. I would have thought it
would show up on the MSDN page I quoted in an earlier reply.

Your unwillingness to learn is so amazing that I wonder why you
bothered to ask at all. There was no notice in the docs because no
feature was removed. It's just that you assumed the existence of a
feature that never was there. Even in SQL Server 2000 queries against
views containing ORDER BY don't always respect any predetermined order.
In many cases ORDER BY in views will be ignored. I believe that has
been true of every version of SQL Server. Your elementary mistake
reminds me of the joke about the economist who sees a brown cow and
therefore assumes all cows are brown.

I'd second Joe's suggestion: get some training. Trial and error is the
most expensive form of education.

--
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/m...S,SQL.90).aspx
--

[Serge Rielau]

RickW wrote:

On saving CPU cycles: I would absolutely agree that a view based on
other views should be parsed and processed so that the ordering in all
underlying views is ignored. Unquestionably beneficial. I just didn't
agree that the ability to retrieve an ordered set from a real-world tool
was an unforgiveable sin against the theoretical definition of the view.
Some do not share this libertine view.

Well, don't mistake my impassionate response for sympathy ;-)

You are correct that your DBMS is a tool, but when the tool starts
violating the very principles upon which it is built that may result in
a tactical raise in customer satisfaction, but causes longterm grief by
pasting the architecture into a corner it can't get out of.
FWIW DB2 behaves exactly the same way for the exact same reasons.
"United we stand" ;-)

Now, lets take a look at the business requirement here you have against
"the tool":
From what I understand you have these views and you'd want that when an
application selects from it it should be able to get results back in an
order which has been defined by the creator of the view. Correct?
If so the question that should be asked (from a language point of view) is:
Does the concept of a "default" or "natural" order violate the
foundation of relational algebra? I think no, as long as this default
order is _explicitly_ requested just like you can explicitly SET c1 =
DEFAULT.

Example:
CREATE TABLE T(pk NOT NULL PRIMARY KEY, c1);
SELECT * FROM T ORDER BY _ORDER_OF_T_;
This could e.g. mean: Please pick up the order defined by the primary
key index.
For "SELECT * FROM V ORDER BY ORDER OF V" this could mean to pick up teh
ORDER BY clause in the view. If you omit this the ORDER BY you get what
you get (no order).

We (IBM) have been mulling this over as an SQL standard submission.
Would be interesting to read the thoughts of Microsoft folks.

As language folks we have to be perceptive to the practical usages of
the language.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[RickW]

I'm not troubled at all by your lack of sympathy for my position. I
learn far more from people who disagree with me than from those who
agree, if the exchanges focus on topic-related information, as yours do.

If I understand correctly, you're mulling over an addition to the
standard, motivated a consideration of actual use of the data. Not that
it should matter, but you absolutely have my vote for that.

Clearly, at some point in the process users will need to see data in a
particular order. In these postings I've seen explicit endorsement for
allowing ordering in a stored procedure, but no viable presentation as
to why it's okay to allow it there but not in a view. Falling back on
"by definition" strikes me as mistaking the map for the territory. In
fact, it's more like believing that the map dictates what the territory
can be.

I have an alternate suggestion for the _order_of_v_ proposal. It seems
to me that taking that approach requires you to create a view B in order
to specify that you want in fact to return a result set ordered by the
specification in view A. Without any new definition element, I could
apply any orering I wanted in a hypothetical view B outside the database
tool. So, although I gain the function, the price is another view.

Would it be just as explicit, and save a lot of view creation, to add an
explicit element following all other SQL, such as [RESULTSORDERED]?
(brackets indicate optional element)

If not present, results would be unordered. That leaves all properly
constructed views working exactly as currently prescribed. If present,
being an explicit element of the definition, it violates nothing but
allows for ordering of a result set. This allow people like me to
continue to bypass creating yet another query outside the tool whenever
possible.

In fact, what I think Microsoft should have done was in effect the
solution I propose above. Following their paradigm for things, that
would show up as a new property on views. By default it would be set to
false. If you wanted to return the results ordered, you would have to
set the property explicitly on each view where it mattered.

Is there is a chance in hell of getting such an element added to the
definition of a view? If so, where do I sign up for the campaign?

Thanks for another post that contributes something to the discussion and
points in an interesting direction. I'm eager to see the responses
myself.
Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[Alexander Kuznetsov]

> Falling back on

"by definition" strikes me as mistaking the map for the territory. In
fact, it's more like believing that the map dictates what the territory
can be.

Rick,

In general I could agree to that. Yet this is a SQL Server newsgroup,
and specifically for SQL Server (as well as for every other RDMBS I
worked with ;) ) it is not the best practice to sort each and every
result set on the server. In many cases sorting could be done just as
easily on the client, and moving some sorts to the client/midle tier in
many cases leads to much better performance. You can read more about it
here:

http://www.devx.com/dbzone/Article/30149

[RickW]

Happy to report, Techniques 1 & 2 in place.

Technique 3: I don't remember having any case statements in order by.
I would check, but I suppose it doesn't matter anyway, since I'm
removing all the order by clauses from the views except where merited by
proper use of Top. In any event, it's a clever tip. Hope I remember it
when the occasion arises.

Technique 4: Very handy, especially as I'm new in .NET and this is a
clearly useful tip. I probably would have by habit put the Order By in
the SQL that goes up to the server. I can imagine that if a have a very
large return (rare in our case), a hefty server compared to the load,
and rather wimpy client machines, I might send the Order By up the line
to let the server do it, but staying mindful of your tip I have the
option to do it only when merited.

Thanks so much for the link.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[Serge Rielau]

Rick,

I don't get your logic forcing the extra view.

Example:
CREATE TABLE T(c1);
CREATE VIEW V AS SELECT * FROM T ORDER BY c1 ASC;

-- I don't care:
SELECT * FROM V => no order

-- I want "the" order:
SELECT * FROM V ORDER BY ORDER OF V => C1 ASCENDING
Note that the clause is rather brain dead. Your .NET/JDBC/PHP client
could tack it on under the covers if you always want it.

-- I want order and I'm going to decide what it is:
SELECT * FROM V ORDER BY c1 DESC => c1 DESCENDING

The problem with your statement level switch is that it is - eh -
statement level.
That is what if you have a nested view somewhere embedded.
You probably don't want the DBMS to sort unless the order finds it's way
to the top.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[--CELKO--]

>> Clearly, at some point in the process users will need to see data in a particular order. <<

My first response was "Which particular order? Why?", but then I
realized that this is not true. I can design a parallel language which
uses no ordering in the outputs.

Is there is a chance in hell of getting such an element added to the definition of a view? If so, where do I sign up for the campaign? <<

You can sign up for the ANSI Standards committee and write a proposal.
But you will need to defend it. And since we have looked at this issue
before, you will have to work at the level of a PhD to get around the
objections and proofs of contradiction that other people found in it.
Let's throw out the foundations of the Relational Model and assume
that we can write crap like

SELECT ..
FROM ..
WHERE ..
[GROUP BY ..]
[HAVING BY ..]
[ORDER BY ..];

If I have ordering on VIEWs, I must have ordering on TABLEs. Or you
have to change the RM to have both ordered and unordered tables with
all the rules for moving data between them.

A. What is the meaning of sorts by things not in the select clause?
Gee, I guess we need to carry extra data on each row for the sort, even
if it is not used.

SELECT a, b
FROM Foobar
ORDER BY x;

Even worse, what is the output of this? Remember that a GROUP BY
destroys the original table to produce a grouped table, so x does not
even exist anymore.

SELECT a, SUM(b)
FROM Foobar
GROUP BY a
ORDER BY x;

B. What is the meaning of sorts on nested subqueries? Which sort has
priority?

SELECT a, c
FROM (SELECT x, y
FROM Barfoo
ORDER BY x) AS XX(a, c)
ORDER BY c, a;

C. What is the meaning of sorts on UNION, INTERSECT and EXCEPT?
Remember that (A UNION B) = (B UNION A), or do you want to throw out
all of Set Theory along with the Relational Model?

SELECT a, b
FROM Foobar
ORDER BY a
UNION ALL
SELECT x, y
FROM Barfoo
ORDER BY x;

What if I add an ORDER BY to the whole thing?

SELECT r, s
FROM (SELECT a, b
FROM Foobar
ORDER BY a
UNION ALL
SELECT x, y
FROM Barfoo
ORDER BY y)
AS XX(r, s)
ORDER BY r, s;

D. What do UPDATE, INSERT and DELETE with ordered table mean? For
example:

INSERT INTO Foobar -- asc ordering
SELECT .. FROM Barfoo; -- desc ordering

E. Another problem here is that there are two kinds of sorts, stable
and non-stable. Now the Standard has to pick an implementation method.
We hate doing that.

Non-stable sorts are much faster than stable (aka "sequence
preserving"); look up QuickSort versus Bubble sort. With the current
cursor model, I have one and only one sort which can be done any way
the optimizer thinks will work best.

If you want non-stable sorting, then only the last sort applied to the
result table matters. But which one is it?? Well, now we need to
specify the absolute order of execution - no optimizer changes,
please.

I also cannot parallelize my code because it would not have an
ordering.

Why is this ordering worth destroying any possible way of having
parallelism or optimization? One of the best things about a VIEW is
that the optimizer can "cut & paste" the VIEW into a query and
fetch the rows in whatever order is required. Your model has to
materialize and then order the table, even if it screws up
optimization.

The advantage of a single ORDER BY in the cursor is that it can be
factored into the optimizer by looking at indexes, or saved for the
middle tier.

[Serge Rielau]

--CELKO-- wrote:
<snip>
Joe,

There is a difference between an ordered table and an object (table,
view, table-function, ..) with which you associate a "suggested order
for your viewing pleasure"
BTW, DB2 has shipped nested ORDER BY in V8 and we have not seen any issues.

What's wrong with allowing the definer of an object to propose such an
order and the user to say: "Hey whatever you say man, I want order, but
I trust you thought about how it looks best."

This is no more dangerous than asking for the special at your local
restaurant. No one forces you to eat it and you don't get it by default
either.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[RickW]

>>You probably don't want the DBMS to sort unless the order finds it's
way to the top.

Definitely.

What I thought you were setting up was this:

View A could specify an Order By clause, but it would not guarantee an
ordered result set.

Then View B could either SELECT x, y, z... FROM A and not invoke the
ordering in the result set or SELECT x, y, z...FROM A ORDER BY
_ORDER_OF_A_, and that would force the result set to return ordered by
the specification in View A.

I think the "SELECT * FROM V ORDER BY ORDER OF V => C1 ASCENDING" view
is what I'm calling View B, the second one that's needed in order to
invoke the ordering.

I was thinking my suggestion would avoid needing B to force the
ordering.

For you is the thing I call View B a query from outside the database
tool, such as a SQL statement passed in from a client, like in the
example you pointed to with your link to the 4 techniques?
Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

RickW wrote:

You probably don't want the DBMS to sort unless the order finds it's

way to the top.

Definitely.

What I thought you were setting up was this:

View A could specify an Order By clause, but it would not guarantee an
ordered result set.

Then View B could either SELECT x, y, z... FROM A and not invoke the
ordering in the result set or SELECT x, y, z...FROM A ORDER BY
_ORDER_OF_A_, and that would force the result set to return ordered by
the specification in View A.

I think the "SELECT * FROM V ORDER BY ORDER OF V => C1 ASCENDING" view
is what I'm calling View B, the second one that's needed in order to
invoke the ordering.

I was thinking my suggestion would avoid needing B to force the
ordering.

For you is the thing I call View B a query from outside the database
tool, such as a SQL statement passed in from a client, like in the
example you pointed to with your link to the 4 techniques?

Views don't return any data. Only the queries issued against a view can
return data to the client. That means either queries in a stored
procedure or in a client application. So those queries would still have
to be the place for your "ORDER BY _ORDER_OF_A_" clause.

There is a problem. The predefined view order can only work if the
column(s) referenced by _ORDER_OF_A_ are also referenced in the SELECT
list of the *query* (not just the view). Otherwise it may not be
possible to determine the correct order and perform the sort. For this
reason, standard SQL (and other SQLs too in many cases) will raise an
error if the ORDER BY columns aren't also included in the SELECT list.
The implication is that the person writing the _ORDER_OF_A _ clause in
the query or proc must A) know which columns determine the order, and
B) include those columns in the SELECT list. So given those constraints
why wouldn't he or she just type the column names in the ORDER BY list
as well?

Remember also that SELECT * is very bad practice and isn't generally
used at all in production code so SELECT * is no good excuse for not
typing an ORDER BY clause. If your syntax is based on the assumption
that SELECT * will be used then it isn't likely to gain wide support
because most developers wouldn't be able or willing to take advantage
of it.

--
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/m...S,SQL.90).aspx
--

[Serge Rielau]

David Portas wrote:

Views don't return any data. Only the queries issued against a view can
return data to the client. That means either queries in a stored
procedure or in a client application. So those queries would still have
to be the place for your "ORDER BY _ORDER_OF_A_" clause. Correct. The need for the query was there before. My proposal does not
change that.
There is a problem. The predefined view order can only work if the
column(s) referenced by _ORDER_OF_A_ are also referenced in the SELECT
list of the *query* (not just the view). Otherwise it may not be
possible to determine the correct order and perform the sort. For this
reason, standard SQL (and other SQLs too in many cases) will raise an
error if the ORDER BY columns aren't also included in the SELECT list. I don't think this is the case in standard SQL (but I don't have the
time to dig through it now. FWIW DB2 does not require the columns to be
specified in the select list.
The implication is that the person writing the _ORDER_OF_A _ clause in
the query or proc must A) know which columns determine the order, and
B) include those columns in the SELECT list. So given those constraints
why wouldn't he or she just type the column names in the ORDER BY list
as well?

Without relaxing the limitation you encountering that would be correct
and indeed undesirable. Given that views get expanded into the query
there is no technical reason for the presence of the order by columns in
the view signature (the columns exposed by the view).
I would guess that SQL Server must know the internal concept of
invisible columns (such as row-id) which is used to pull through order
without infesting the exposed select list.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[David Portas]

Serge Rielau wrote:

Given that views get expanded into the query
there is no technical reason for the presence of the order by columns in
the view signature (the columns exposed by the view).

So what would you expect to be the order of rows output by ORDER BY
_ORDER_OF_v1_ in the following example? Are you sure you can define
that order for all possible queries? Does it make a difference if I
leave Z in the view or not?

I suspect that any deterministic logic you define is going to be so
obscure that it would outweigh any apparent attraction of the syntax
shortcut you are proposing. It would cause more confusion and
unjustified assumptions about what the "correct" order should be. In
other words it would add nothing in terms of clarity or concision.

CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
(x,z));

INSERT INTO tbl (x,z) VALUES (100,1);
INSERT INTO tbl (x,z) VALUES (100,2);
INSERT INTO tbl (x,z) VALUES (200,0);
INSERT INTO tbl (x,z) VALUES (200,4);

CREATE VIEW v1 AS
SELECT x,z
FROM tbl
ORDER BY z /* note: not legal SQL */

SELECT x
FROM v1
GROUP BY x
ORDER BY _ORDER_OF_v1_ ;

--
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/m...S,SQL.90).aspx
--

[Serge Rielau]

David Portas wrote:

Serge Rielau wrote:

Given that views get expanded into the query
there is no technical reason for the presence of the order by columns in
the view signature (the columns exposed by the view).

So what would you expect to be the order of rows output by ORDER BY
_ORDER_OF_v1_ in the following example? Are you sure you can define
that order for all possible queries? Does it make a difference if I
leave Z in the view or not?

I suspect that any deterministic logic you define is going to be so
obscure that it would outweigh any apparent attraction of the syntax
shortcut you are proposing. It would cause more confusion and
unjustified assumptions about what the "correct" order should be. In
other words it would add nothing in terms of clarity or concision.

CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
(x,z));

INSERT INTO tbl (x,z) VALUES (100,1);
INSERT INTO tbl (x,z) VALUES (100,2);
INSERT INTO tbl (x,z) VALUES (200,0);
INSERT INTO tbl (x,z) VALUES (200,4);

CREATE VIEW v1 AS
SELECT x,z
FROM tbl
ORDER BY z /* note: not legal SQL */

SELECT x
FROM v1
GROUP BY x
ORDER BY _ORDER_OF_v1_ ;

Very good example. Now this of course cannot preserve the order.
One might argue that it should, in fact, return an error.
But does a feature of convenience need to work under all circumstances?
This query also is not updatable, or deletable yet SQL clearly supports
updatable cursors. It simply slaps your wrists when you ask for semantic
nonsense.
Here the user chose to further process the rows of the view in way that
cannot preserve order. That does not invalidate the usefulness of the
proposal IMHO.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[David Portas]

Serge Rielau wrote:

Here the user chose to further process the rows of the view in way that
cannot preserve order. That does not invalidate the usefulness of the
proposal IMHO.

So here's the problem. There are many, many examples using joins,
unions, aggregations and projections where ordering cannot sensibly be
permitted based on the underlying view(s). So do you want to detect
these at design time and raise an error or do you just give a warning
and allow creation of a query with the ORDER BY clause that won't in
fact be ordered?

If you allow the creation of queries and views that have these invalid
ORDER_OF_? clauses then what if the ORDER BY clause on the base view
changes? The person creating the view will presumably see a warning but
what about the users of queries against that view? They will see
nothing wrong. The users' queries will still have the same ORDER_OF_?
clauses and they will even appear to work normally. They may even
display the same ordering sometimes but then at other times they may
show a different order because in reality no logical ordering is
actually taking place. Now if the users are anything like RickW they
will be saying "My view has an ORDER BY but it isn't ordered. It used
to work in the old version. IBM stinks. They did this to me without any
advance warning."

I'd say the only reasonable solution is to validate the ORDER_OF_?
clause EVERY TIME a query is created or executed and to disallow the
query if the ordering is a non-permitted one. This would surely be
incredibly frustrating to the user unless he knows the column names
being used in the ordering or has access to that information. Only if
the user developing the query knows what the order is can he know
whether or not it is appropriate to use ORDER BY ORDER_OF_?.

So haven't we come full circle? Not only does the user have to know
which views contain ORDER BY clauses he also has to know what the order
is. If the user KNOWS what the ordering is and has to take that into
account when designing the query then what do we gain from the
ORDER_OF_? clause? Is this really only about saving a few keystrokes
and does that really justify the added complexity of validating views
and queries?

--
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/m...S,SQL.90).aspx
--

[RickW]

David Portas wrote "... then what if the ORDER BY clause on the base
view changes?"

If it changes, it changes, and you (or at least I) would hope that the
designer in charge of the base view would understand that design changes
of that nature should come in response to user request, formal review,
and acceptance of the effect, not in silence just because the designer
felt like it.

I don't think that this possibility supports your argument.

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

RickW wrote:

David Portas wrote "... then what if the ORDER BY clause on the base
view changes?"

If it changes, it changes, and you (or at least I) would hope that the
designer in charge of the base view would understand that design changes
of that nature should come in response to user request, formal review,
and acceptance of the effect, not in silence just because the designer
felt like it.

I don't think that this possibility supports your argument.

*** Sent via Developersdex http://www.developersdex.com ***

I agree with you. My principal objection is not that ordering views
makes change management harder (although it must do to some degree). My
argument is that for it to work the users must be aware of what
ordering exsits in the view and must take account of that when
designing their queries. So in my opinion there is no usability benefit
to be had from an ordered view other than saving the time it takes to
type in the column names.

If saving keystrokes is your goal then more benefit could be obtained
from creating a better development environment. I'm talking about
features like the object browser in Management Studio that save you
from typing lists of column names.

--
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/m...S,SQL.90).aspx
--

[Serge Rielau]

David Portas wrote:

Serge Rielau wrote:

Here the user chose to further process the rows of the view in way that
cannot preserve order. That does not invalidate the usefulness of the
proposal IMHO.

So here's the problem. There are many, many examples using joins,
unions, aggregations and projections where ordering cannot sensibly be
permitted based on the underlying view(s). So do you want to detect
these at design time and raise an error or do you just give a warning
and allow creation of a query with the ORDER BY clause that won't in
fact be ordered?

Actually not UNION.
SELECT * FROM (SELECT c1 FROM T1 UNION ALL SELECT c1 FROM T2) AS X
Does not allow you to reference T1 or T2.
If your view contains the UNION then of course it can order the result
of the union.

There are to orthogonal pieced here:
Defining a "suggested" ordering and consuming that ordering.
Let's assume I have created a view with an order by.
Let's assume I have written some queries that consume this order by
correctly (no aggregation, funny join).
Now I alter the view and I change the order by clause.
Nothing will change. all queries will continue to work using the new
order. this is no different than if you alter the DEFAULT of a column.
It doesn't break any INSERT or UPDATE statements.

Let's go back to the original problem:
The OP wanted to _encapsulate_ the order inside of an object (preferably
a view). This is absolutely achieved.
If the query succeeded against the object once it will always succeed,
because the only way to break the order by is through bad specification
of the query itself.
If you screw up the ORDER BY for a result set in a procedure exactly the
same implications arise. Of course the app will see the difference and
presumably this is the whole idea.

W.r.t. what happens in the bad case: Yes, of course the DBMS should
raise and error if it can't do what the user requests.
I see no point, btw., in raising an error when the underlying object has
NO ORDER BY to begin with.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[RickW]

David Portas wrote: My argument is that for it to work the users must
be aware of what ordering exsits in the view and must take account of
that when designing their queries. So in my opinion there is no
usability benefit to be had from an ordered view other than saving the
time it takes to type in the column names.

Some of the back-and-forth in this thread stems from a difference in
what is meant by users. From where I sit, users do not design queries.
In your sense, I am the user, the only user. I will always know what
columns are available.

Since I design the views and also consume them in my application front
end, there is usability benefit to be had in knowing that I have a
single, ordered source of a particular kind of data on the server, and
that I do not have to re-create the SQL for that anywhere in the
application, not even as a constant or in a config file.

I haven't disagreed with the definition-driven arguments, insofar as
I've understood them. I only disagree with the idea that, when building
a tool, such a definition is the only factor to consider. Especially a
definition that takes no notice of a major reason for collecting and
presenting data: to show an application user something in a certain
order.
Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[Doug]

RickW,

Another solution is to go back to SQL Server 2000.

In Access, can you ask "Access" to return your result sets in order?
Perhaps that MIGHT force the SQL getting the data from the view to
encourage a sort order. Can you put that code into the link getting the
data?

This is an interesting little hole.

I will submit that we have indeed found a "bug." If order by is not
"legal" for a view, then why does it compile, and not raise an error
when you save it off?

SQL Server is a product. It is being sold to CUSTOMERS. Customers do
not care whether something is intrinsicly internally blah blah blah.
Customers care that the product solves a need. Is it better to be
"right" and broke, or is it better to have happy customers?

I would further suggest this demonstrates views on the whole are not
used in a production environment.

I hate views. They are inefficient, they are slow, and they do not
allow flexibility.

What is a table? I think of it as a set of data. What is a view? A
view is a derived extremely slow set of data.
What does a stored procedure return? Why, it returns very quickly the
specific set of data that I am interested in

[Alexander Kuznetsov]

> Is it better to be "right" and broke, or is it better to have happy customers?

Neither - it's better to be right and prosperous, and have happy broke
customers ;)

[RickW]

Doug wrote: Another solution is to go back to SQL Server 2000.

Not merited by this little hiccup.

Doug wrote: In Access, can you ask "Access" to return your result sets
in order?

Absolutely. We already have hundreds of queries in Access, and about 60
places in code where we are setting SQL up for lists. Dumping the Order
By spec in 31 views was a minor nuisance, not much more. Because Access
is sufficiently object oriented to use it on itself, I wrote some code
to look through query SQL, form recordsources, and list control
rowsources to find any reference to one of the affected views without an
accompanying Order By.

Not so terribly painful, just hacked me off that something I know is
used, documented or not, would be dropped without more noise.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

Doug wrote:

I will submit that we have indeed found a "bug." If order by is not
"legal" for a view, then why does it compile, and not raise an error
when you save it off?

ORDER BY is valid in views only when used in conjunction with TOP. Its
purpose in a view or derived table is solely to filter the set of rows
selected with the TOP clause. It does NOT determine the order of rows
returned from a query issued against that view. So in fact ORDER BY is
doing exactly what it is supposed to do and there is no bug.

Microsoft's choice of syntax for the TOP modifier is certainly poor. It
is confusing that ORDER BY serves two purposes. That problem is
remedied somewhat in SQL Server 2005 by the addition of the SQL OLAP
functions that have their own dedicated ORDER BY clause. So bad design,
yes. Bug, no.

--
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/m...S,SQL.90).aspx
--

[Doug]

>ORDER BY is valid in views only when used in conjunction with TOP

but if i have an order by in a view, without the TOP, it compiles.

Therefore, it is a bug.

As an example, If I use "sum" without a group by phrase, it won't
compile.

[--CELKO--]

>> I hate views. They are inefficient, they are slow, and they do not allow flexibility. <<

Actually they can be VERY efficient in most products. If the text of
the VIEW definition is copied as an "in-line macro", it works just like
a CTE or derived table. That is rather fast.

If the VIEW definition is materialized, several products will share
that materialized table in cache. Oracle has this feature, for
example.

[David Portas]

Doug wrote:

ORDER BY is valid in views only when used in conjunction with TOP

but if i have an order by in a view, without the TOP, it compiles.

That would indeed be a bug but I'm not aware of it. Can you post some
code to reproduce that behaviour and state what version, edition and
service pack you are using? What you should see is an error message.
Following example tested on Dev Ed. SQL Server 2000 - 8.00.2039 SP4:

CREATE TABLE T1 (x INT);
GO
CREATE VIEW v1 AS SELECT x FROM T1 ORDER BY x
GO

Result:

Server: Msg 1033, Level 15, State 1, Procedure v1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.

--
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/m...S,SQL.90).aspx
--

[Bart]

"RickW" <user=wannall at=@ other=sbcglobal final=.net> wrote in message
news:o_***************@news.uswest.net...

Ignoring the rude, ego-inflated hot air that blasts off the page, I
reply with a few details.
Let me think...Wouldn't I love to hire someone who loves to insult
people, who writes like an angry teenager, and who overrates his own
Damn that's funny. Joe didn't even take out the big guns to insult you and
you are crying. Generally speaking, if you want to ask an SQL question,
listen to Joe. If you want to feel good about yourself, ask some else
offline. Joe and a few others have been INCREDIBLY helpful in finding SQL
solutions. Just check your ego at the door. Besides, Joe is a teddy bear
if you meet him in person and pretty funny if you get him drunk.

understanding and abilities with every pronouncement? And who wraps
exactly one informative sentence ("a VIEW is a table and **by
definition** has no ordering") in 10 ranting insults? Hmmmm. Tough
call.

Come to think of it, I'm curious: Why exactly did you bother to reply?
Did you accomplish what you intended? What exactly was that?


Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[Herb Caudill]

Thanks to everyone who posted to this very informative thread. I agree
with RickW that this is a very annoying change in SQL Server's behavior.
Microsoft has a reputation for being very careful of protecting
backwards compatibility by not changing software behavior that
developers have come to rely on, whether that behavior was documented or
not. At the very least this should be included in the migration white
paper as an issue to look out for.

To the SQL theorists and purists: SQL Server is a tool that is being
effectively used by a lot of people for a lot of different purposes.
Some of these people aren't Ph.D.s; some of them have (gasp) no formal
training at all. Not every organization has a professional DBMA to do
database architecture for them, and they shouldn't be required to.

Take me: I am a web developer. I use SQL server to store content for
small websites. I'm 100% self-taught, and my company is in no danger of
failing as a result, thank you very much. I don't give a rat's ass about
query optimization or performance; CPU cycles are cheap and my datasets
are small. I want to push my grouping/selecting/ordering logic into SQL
as much as possible; so for instance I might create a base view that
selects out content that is approved to display on the website and puts
it in the correct order, and then create other views that select subsets
of that view for specific pages, perhaps performing joins or what have
you along the way. This way the select & sort logic lives in one place,
and if it changes I only have to change it once. This might turn Mr.
Celko's hair green because it won't scale to a dataset with ten million
records, or because it violates some aspect of relational set theory
that he learned in RDBMS class - but it worked fine for my purposes.

Now, because ivory-tower purists like Celko apparently won an internal
debate at Microsoft, I'm going to have to either (1) take the same
sorting logic and reproduce it fifteen different places, because my SQL
code can't be relied on to return ordered data; or (2) rewrite the .NET
objects that talk to my database so that they use stored procedures
instead of views. Terrific. You guys win.

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

Herb Caudill wrote:

Now, because ivory-tower purists like Celko apparently won an internal
debate at Microsoft, I'm going to have to either (1) take the same
sorting logic and reproduce it fifteen different places, because my SQL
code can't be relied on to return ordered data; or (2) rewrite the .NET
objects that talk to my database so that they use stored procedures
instead of views. Terrific. You guys win.

I doubt that there was any internal debate about purism. This is simply
about improving the product. SQL Server 2005 adds enhancements to the
optimizer that allow it to make better query plans so that it can run
queries more efficiently. As it happens those enhancements have the
effect that some queries without ORDER BY may be sorted differently
relative to 2000 in some cases.

You could argue that MS should have included the ability to disable
those enhancements and revert to the old engine behaviour but that
could be a very hard thing to accomplish. How is Microsoft supposed to
know exactly which undefined row orderings were important to developers
in past versions? Guaranteeing unchanged row order in every case would
be a practical impossibility I suspect.

So Microsoft listens to its customers when deciding where to invest in
product development. Most of Microsoft's customers DO demand more
performance and better scalability from each new release. Many of those
customers wouldn't want to see other improvements compromised in an
effort to second-guess developers by making some previously unreliable
behaviour into a new product feature.

--
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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

Herb Caudill (he**@caudillweb.com) writes:

Take me: I am a web developer. I use SQL server to store content for
small websites. I'm 100% self-taught, and my company is in no danger of
failing as a result, thank you very much. I don't give a rat's ass about
query optimization or performance; CPU cycles are cheap and my datasets
are small.

I can assure you that very many users of SQL Server do care about
performance.

Being 100% self-taught is admireable, not the least because it includes
learning some things the hard way, even the basics. As for instance that the
only way to get a certain order out of a query is to use ORDER BY in it.
I'm sorry, but that is way SQL works, not only SQL Server.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Herb Caudill]

Of course lots of people care about performance, and they should. My
point is that performance is not the only thing that matters, something
that seems lost on many contributors to this newsgroup. If you have a
one huge database and lots of developers, it makes sense to focus on
performance. If you have lots of small databases and just a couple of
developers, which is my situation, it makes better business sense to
focus on developer productivity instead. For me database performance and
scalability are simply non-issues. On the other hand, code reuse,
encapsulation of logic, and things like that are absolutely paramount.
(Here's a horrifying example for you: We deliberately use SELECT * where
possible, because it means that we can add a field to an application and
only have to touch our code in three places, instead of twenty.)

In the past Microsoft has been very good about balancing performance
against developer productivity, and about preserving backward
compatibility as well. There are a number of solutions that they could
have come up with here that would have been preferable to breaking
existing applications that depended on an application's previous
behavior, even when that behavior was undocumented.

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, and I depended on it. The fact that there was a
footnote in an RFC or a technical specification somewhere the explained
that this was not guaranteed is beside the point; as far as I'm
concerned this is a breach of backward compatibility, and your
explanation that the new approach gives me better query optimization
does not interest me at all.

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? Most intelligent programmers approaching SQL for the
first time would assume that ordering of data is a service that a
database language should be able to provide - indeed, one that is best
left to the database as opposed to the client code - and that this
service could be incorporated into objects (views/functions/procedures)
that can in turn be used by other objects. I understand the performance
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.

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

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/m...S,SQL.90).aspx
--

[Herb Caudill]

>> You didn't read this thread very well. A view or a function does NOT
return any data <<

Whatever. That seems to me a very abtruse point to make in response to a
practical question.

Maybe the problem here is that I want SQL to be something more than it
is. As an object-oriented programmer, I expect to be able to encapsulate
logic at the lowest possible level. In this situation, "logic" includes
sorting logic. The conclusion on this thread is that there's no way to
do that - that in a chain of nested views/functions, the sorting logic
has to take place at the very last minute - which means that it has to
be reproduced and maintained in many different places. That's
unsatisfactory.

A similar problem having to do with encapsulation - perhaps a topic for
another thread - is that I can't write a generic function to perform
consistent operations against multiple tables. For example: Most of my
applications have a number of tables that are all subject to the same
versioning and approval logic, which is a little complex. A table-valued
function like this would be very useful to me:

Create Function GetApprovedItems(TableName as varchar(50)) returns Table

Of course, I can't do that because it would require building and
executing a command that contains the name of the table, and you can't
have an EXEC statement in a function. I could do something like this in
a stored procedure, but I can’t perform any selects or joins on a stored
procedure, so that does me no good. I'm sure all you relational data
theorists can all jump in right now and lecture me about why functions
can't include non-deterministic blah-blah-blah, but I don't care. The
point is that I don't have any good options. Here they are:

1. Write the same function many times, just varying the table names
(GetApprovedArticles, GetApprovedProjects, etc). This is my current
solution, but it's unsatisfactory. If I need to tweak the approval
logic, I then have to do it in several different places.

2. Build the SQL functions programmatically within my application. This
is an approach I considered, but it makes things complicated in other
ways.

3. Push all of this logic to the application (e.g. pull in raw data and
perform all the selecting and sorting using business objects in C# or
VB). This has two problems: (a) I lose all of the SQL’s power and
elegance for selecting subsets, joining related tables, and so on; and
(b) I lose a lot of flexibility in terms of adapting my framework to new
data architectures. As it is, adding a new field – or even a new table
for holding website content – is relatively easy. Adding a whole new
layer of plumbing to maintain would eliminate that flexibility. I had
just one big, stable database to worry about, this is probably the
approach I would take; but the strength of my website framework is that
I can easily adapt it to a new customer’s information structure with
relatively little work, which keeps prices reasonable for my customers
and gives me a viable business model.

Is it unreasonable of me to think that at this late date, we ought to
have a superset of SQL that provides some of the benefits of an
object-oriented approach (abstraction, encapsulation etc.)? Or is that
just crazy talk?

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

Herb Caudill wrote:

Maybe the problem here is that I want SQL to be something more than it
is. As an object-oriented programmer, I expect to be able to encapsulate
logic at the lowest possible level. In this situation, "logic" includes
sorting logic. The conclusion on this thread is that there's no way to
do that - that in a chain of nested views/functions, the sorting logic
has to take place at the very last minute - which means that it has to
be reproduced and maintained in many different places. That's
unsatisfactory.

A similar problem having to do with encapsulation - perhaps a topic for
another thread - is that I can't write a generic function to perform
consistent operations against multiple tables. For example: Most of my
applications have a number of tables that are all subject to the same
versioning and approval logic, which is a little complex. A table-valued
function like this would be very useful to me:

Create Function GetApprovedItems(TableName as varchar(50)) returns Table

Why would you want to parameterize the table name in this case? I can't
be sure without seeing your table structure but one mistake commonly
made by OO programmers is to use tables like an object model - as
containers for distinct subsets of the same type of data. That gives
poor results in the relational model.

The relevant design principle is that of Orthogonal Design. Loosely
speaking we sometimes say that no two tables should share the same key
and and non-key attributes. If you follow that principle then I don't
know why you would want the same approvals process to apply to two
tables. The tables should not "overlap" so there should be no processes
that they have in common. Maybe what's missing from your model is
another table called "Approvals" - but there I'm just guessing.

Every software development discipline has its own design patterns and
practices. In the case of the relational model we are fortunate that
these are very well developed and documented. If you follow the right
design pattern then SQL should have all you need to encapsulate logic
and enable code re-use. To implement an inappropriate design and then
say that it demonstrates deficiencies of the underlying model isn't a
productive course to take whether in SQL or in an OO language or in any
other environment.

--
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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

Herb Caudill (he**@caudillweb.com) writes:

Maybe the problem here is that I want SQL to be something more than it
is. As an object-oriented programmer, I expect to be able to encapsulate
logic at the lowest possible level.
An error which is not uncommon to make when you have a very good hammer,
is to also use it to draw screws.

The object-oriented model is very elegant, and has its benefits.

But a relational database is not in Kansas, and applying too much O-O
thinking in a relational database is going to lead you into problems.
Not because O-O is inferior or relational is better, but just because it's
different. Object-oriented databases have seen the light of day, but they
never took off, for reasons unknown to me. The reason relational databases
have become so dominating is that they have proven to be able to handle
huge volumes of data with good performance to reasonable development costs.
A similar problem having to do with encapsulation - perhaps a topic for
another thread - is that I can't write a generic function to perform
consistent operations against multiple tables. For example: Most of my
applications have a number of tables that are all subject to the same
versioning and approval logic, which is a little complex. A table-valued
function like this would be very useful to me:

Create Function GetApprovedItems(TableName as varchar(50)) returns Table
I don't know about your databases, but usually when people want to
parameterise on the table name, there is a design flaw. Many tables should
really have been one table, with one more key column.

It's important to understand that in a relational database, tables are
very unique entities. If you have two functions that have exactly the
same code, save the table name, that does not mean that they will execute
the same. In fact, even if the table name is the same, but the databases
are different, the query plan will be different.

Here is a very important difference to traditional programming, including
O-O. Here the program code is a very detailed instruction on what operations
to do, in which order to access data etc. There is an optimizer, but it
mainly manages smaller parts.

But in SQL you basically only describe what result you want, the optimizer
will find out the best way to compute that result. All tables in SQL Server
has statistics, and the statistics are unique to the table, because the
statistics describe the data in table, and different statistics gives
different query plans.

An implementation or table-name parameters would certainly lead to that
the same module could have multiple query plans, and it could be quite
confusing for pprogrammer that do not understand this.

Again, while you don't care about performance, most users of SQL Server do,
and focus on performance will always be very stronng in SQL Server.
can't include non-deterministic blah-blah-blah, but I don't care. The
point is that I don't have any good options. Here they are:

There is one more that you did not mention: you can use a pre-processor.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

i think you guys are focusing on trees, where the CUSTOMER is seeing
the forest.

SQL provides data to applications. This guy is saying SQL could do a
better job of it.

[Erland Sommarskog]

Herb Caudill (he**@caudillweb.com) writes:

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, and I depended on it. The fact that there was a
footnote in an RFC or a technical specification somewhere the explained
that this was not guaranteed is beside the point; as far as I'm
concerned this is a breach of backward compatibility, and your
explanation that the new approach gives me better query optimization
does not interest me at all.

Permit me to point that it is a matter of a "footnote", but a very
basic property of SQL. queries return tables, and tables are unordered
sets. If you need a certain order from a query, you need to use ORDER BY.
This apply to all RDBMS products.

This is no stranger that in a C program you should initialize all your
variables, although you in some cases can assume that will have a certain
value.

By the wau, I would like to express the hope that you in some few years
will start to care about performance. Because that would mean that your
business has taken off.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[RickW]

Erland wrote <If you need a certain order from a query, you need to use
ORDER BY.>

We all know that you need to use ORDER BY. We just thought it should be
available in a query stored as a view in SQL Server.

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.

I agree with Herb. It was lazy of Microsoft to omit a solution to this.
I'm lucky. I have one application of 60K lines of VB, and only about 30
views relied on ORDER BY to present ordered data to the application.
Moving the ORDER BY to the application side was not a big deal for our
app. I can sure see how a lot of developers could be in a much worse
situation.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

RickW (user=wannall at=@ other=sbcglobal final=.net) writes:

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.
A view is not a query. If you want stored queries, use stored procedures.
The purpose of a view is gather information from one or more tables, to
make it easier for end users to work with the data, or to hide information
they are not supposed to see. But it is still an unordered set of
information.
I agree with Herb. It was lazy of Microsoft to omit a solution to this.

There are stored procedures, and thus there is a solution.

What was a mistake was to supply a syntax that lead you to believe that
you could do this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[RickW]

I am absolutely fascinated by the complete lack of ability of the
theory-side respondents to differentiate between a thing in SQL Server
that accidentally has the object name "view", and the theoretical
construct defined by the term <VIEW>.

Amusingly, the accidental fact that there doesn't happen to be a
contravening definition of a construct named <STORED PROCEDURE> causes
them to accept without noticing any contradiction that it therefore
makes sense for an object in SQL Server with object name "stored
procedure" to return an ordered set.

Theory absolutely rules, right up to that layer where the tool meets the
real world. Right there, at that interface, you start accommodating how
a thing must be used (user interface), by adapting the things that must
work a certain way (underlying mechanisms).

Failure to do so leads to the kind of nonesense that just happened with
ORDER BY.
Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

RickW (user=wannall at=@ other=sbcglobal final=.net) writes:

I am absolutely fascinated by the complete lack of ability of the
theory-side respondents to differentiate between a thing in SQL Server
that accidentally has the object name "view", and the theoretical
construct defined by the term <VIEW>.

Amusingly, the accidental fact that there doesn't happen to be a
contravening definition of a construct named <STORED PROCEDURE> causes
them to accept without noticing any contradiction that it therefore
makes sense for an object in SQL Server with object name "stored
procedure" to return an ordered set.

Theory absolutely rules, right up to that layer where the tool meets the
real world. Right there, at that interface, you start accommodating how
a thing must be used (user interface), by adapting the things that must
work a certain way (underlying mechanisms).

Failure to do so leads to the kind of nonesense that just happened with
ORDER BY.

When you want a query against a view to return data in a order as
defined in a view, you are not asking for a view, you are asking for
a stored query. Which is essentially is a stored procedure.

But, yes, if you insist on that to equat views with stored procedures
it will lead to nonsense.

Each trade has its fundamentals, one fundamental in SQL is that if you
want an order from query, you must use ORDER BY. You can call it theory
if you like. But it's really more like "that's the way it works".
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>> I am absolutely fascinated by the complete lack of ability of the theory-side respondents to differentiate between a thing in SQL Server that accidentally has the object name "view", and the theoretical construct defined by the term <VIEW>. <<

And I am absolutely fascinated by people who how no idea about
foundations and basic terms. "Caesar: Pardon him, Theodotus. He is a
barbarian and thinks the customs of his tribe and island are the laws
of nature." - Caesar and Cleopatra; George Bernard Shaw 1898.

Ghod! Next thing you know, we ANSI/ISO Industry standard Mathematical
types will want 2+2 = 4 and that Humpty Dumpty cannot make up things on
the fly.

Amusingly, the accidental fact that there doesn't happen to be a contravening definition of a construct named <STORED PROCEDURE> causes them to accept without noticing any contradiction that it therefore makes sense for an object in SQL Server with object name "stored procedure" to return an ordered set. <<

Actually, the Standards have SQL/PSM for stored procedures and it does
not make "ordered sets" -- whatever the heck that might be.

[--CELKO--]

>> Microsoft has a reputation for being very careful of protecting backwards compatibility by not changing software behavior that developers have come to rely on, whether that behavior was documented or not. At the very least this should be included in the migration white paper as an issue to look out for. <<

Actually, SQL Server has been pretty about getting in line with
ANSI.ISO Standards -- OUTER JOINs, the ALL() predicate, etc.

No obligation to document on the part of a vendor? I have mixed
feeling about that .. and a lawyer! And a user group!

But any developer who codes based on needless proprietary features is
a an amatuer or a hillbilly who cannot speak the language properly.
Anyone who depends on undocumented proprietary features is a dangerous
moron. And, yes, those bad programmers will have to clean up their
crap in dozens of places. This is how Ghod punishes you for your bad
code.

Or how Satan punishes the next guy for YOUR mistakes when he has to
maintain it. A professional would have written code for the next guy.
The amateur amuses himself.

[--CELKO--]

>> Of course lots of people care about performance, and they should. My point is that performance is not the only thing that matters, omething that seems lost on many contributors to this newsgroup. <<

I agree!

80%+ of the cost of a system is in maintaining it over its lifetime.
Ergo, following standards so that any programmer who speaks the
language can read and work with the code base is the biggest source of
total improvement. Read any SEI, DoD etc. paper for the last 30 years
about the total cost.

I feel like I am teaching a freshman SE class again ..

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. <<
So, if most people think 2 + 2 = 5 then the accounting package should
change? Why, of course! We want to have the most imcompetent people
writing code and that the programs produce crap, run slow, etc. because
it might hurt their feelings if their invincible ignorance is not made
law.
there are any number of ways that Microsoft could have pleased me *and* the massive-scalability crowd: <<

No, not really. If you mean VLDB (with few users -- DW), then the use
of physically contigous storage has frozen SQL Server at a certain
level. You then go to Teradata and SAND and some other products.

If you mean lots of users with a relatively small RDBMS like a
website, then this is another game Much of that work has to be done in
a middle tier, mirrors, etc.

It is not a simple matter of ORDER BY being turned on or off. It is a
matter of parallelism versus sequential processing. We are not talking
about 1-2 orders of magnitude -- there can be at 7-8 orders of
magnitude difference as it tries to preserve ordering.
..

[Herb Caudill]

>>> incompetent people ... ignorance ... amatuer or a hillbilly ...
dangerous moron ... clean up their crap ... This is how Ghod punishes
you for your bad code ... Satan ... etc. <<<

My goodness, Celko. Where is this anger coming from? A little out of
proportion to the subject matter, if you ask me. I'd genuinely like to
learn from you, since you obviously know more than I do, but the
bile-to-wisdom ratio of your posts makes it tempting to just ignore you.
That would be a shame, wouldn't it?

Here's a simple real-life situation; let's see if we can work together,
keep our blood pressure down, and come up with a solution that is both
practical and theoretically pure.

1. You have a SQL table containing website content.
2. 25 different parts of the website display 25 different subsets of
this content.
3. The content on every page should be sorted in exactly the same way.
The sorting logic is relatively complex and subject to change.

Here's how I might have handled this on SQL Server 2000:
1. Create a base view that incorporates the sorting logic: Select top
100 percent ... order by ... (the sound you hear is Celko calling down
divine wrath upon me and my children and my children's children)
2. Create 25 child views to return subsets of the base view.

Of course, now that I've been punished by the almighty for my crimes
against humanity, this is no longer an option. Some suggest moving the
sorting logic to the client - which would require the same exact "order
by" clause 25 places in my .NET code. This is unsatisfactory because I
have to maintain the same logic in 25 different places. Can we do
better?
*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

Herb Caudill wrote:

incompetent people ... ignorance ... amatuer or a hillbilly ...

dangerous moron ... clean up their crap ... This is how Ghod punishes
you for your bad code ... Satan ... etc. <<<

My goodness, Celko. Where is this anger coming from? A little out of
proportion to the subject matter, if you ask me. I'd genuinely like to
learn from you, since you obviously know more than I do, but the
bile-to-wisdom ratio of your posts makes it tempting to just ignore you.
That would be a shame, wouldn't it?

Here's a simple real-life situation; let's see if we can work together,
keep our blood pressure down, and come up with a solution that is both
practical and theoretically pure.

1. You have a SQL table containing website content.
2. 25 different parts of the website display 25 different subsets of
this content.
3. The content on every page should be sorted in exactly the same way.
The sorting logic is relatively complex and subject to change.

Here's how I might have handled this on SQL Server 2000:
1. Create a base view that incorporates the sorting logic: Select top
100 percent ... order by ... (the sound you hear is Celko calling down
divine wrath upon me and my children and my children's children)
2. Create 25 child views to return subsets of the base view.

Of course, now that I've been punished by the almighty for my crimes
against humanity, this is no longer an option. Some suggest moving the
sorting logic to the client - which would require the same exact "order
by" clause 25 places in my .NET code. This is unsatisfactory because I
have to maintain the same logic in 25 different places. Can we do
better?

Use a stored proc. I don't know why that would be a problem for you.
Best practice is to use procs for ALL data access. Don't ask why procs
can sort and views can't. I've no intention of repeating myself again
:-).

--
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/m...S,SQL.90).aspx
--

[Herb Caudill]

But stored procedures are a dead end - you can't refer to them in any
other objects. I can't select subsets of the results of a stored
procedure, I can't join against it, I can't count its results without
using cursors (not in SQL, anyway).

So if I replace my base view with a stored procedure, I can't do
anything further with it in SQL, and the client has to do everything.
The .NET code for each section of the website has to request the entire
set of content items, and then choose the ones it wants. That's no good
for two reasons. One, each request is pulling far more data than it
really needs. Two, SQL is far better at selecting subsets than .NET is.

Surely we can do better.

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

Herb Caudill wrote:

But stored procedures are a dead end - you can't refer to them in any
other objects. I can't select subsets of the results of a stored
procedure, I can't join against it, I can't count its results without
using cursors (not in SQL, anyway).

But if you wanted to do more stuff in SQL then you'd put that in a proc
as well.
So if I replace my base view with a stored procedure, I can't do
anything further with it in SQL, and the client has to do everything.
The .NET code for each section of the website has to request the entire
set of content items, and then choose the ones it wants. That's no good
for two reasons. One, each request is pulling far more data than it
really needs. Two, SQL is far better at selecting subsets than .NET is.

So use parameterized procs that only return exactly what is needed.

There are lots of excellent reasons why we use procs for all things
you've described. Here are some links:

http://msdn.microsoft.com/library/de...un_1a_6x45.asp
http://www.sql-server-performance.co...procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/archi.../17/38095.aspx

--
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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

Herb Caudill (he**@caudillweb.com) writes:

But stored procedures are a dead end - you can't refer to them in any
other objects. I can't select subsets of the results of a stored
procedure, I can't join against it, I can't count its results without
using cursors (not in SQL, anyway).

So if I replace my base view with a stored procedure, I can't do
anything further with it in SQL, and the client has to do everything.
The .NET code for each section of the website has to request the entire
set of content items, and then choose the ones it wants. That's no good
for two reasons. One, each request is pulling far more data than it
really needs. Two, SQL is far better at selecting subsets than .NET is.

I can't really get this. If you make your view and then make further
selections from it. What reason do you have to expect that there will
be some inherent sorting into it?

The purpose of TOP n PERCENT ORDER BY in a view is to define a subset
by some criteria - not to specify how the data is created. You've taken
the feature and tied your own definition to it. That usually comes back
and bite you one day.

Maybe you should look into the row_number function. This permits you
to number rows in a query according to some criteria. The final queries
that output data would need to order by this column, but the definition of
the row-number column would have the complex sorting logic.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Herb Caudill]

>> You've taken the feature and tied your own definition to it. That
usually comes back and bite you one day. <<<

OK, OK, OK. Got it. Point is it used to work that way and all of us
stupid schlubs who failed to read the fine print took advantage of the
way it worked in practice.

I'm trying to arrive at some sort of best practice now.

Maybe you should look into the row_number function <<<

Thanks - I'll look into this. At first glance, it seems like a very
complicated solution to (what used to be) a very simple problem.

*** Sent via Developersdex http://www.developersdex.com ***