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