|
|
Posted by David Portas on 03/08/06 18:41
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/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|