Re: ORDER BY in VIEW not working

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

.

Relevant Pages

  • Re: Views
    ... SQL Server sometimes decides to materialize the view ... into memory before applying the where clause. ... complicated queries, ... then you appear to have run into a bug. ...
    (microsoft.public.sqlserver.clients)
  • Re: Transaction problem (delete / select)
    ... It is not a bug. ... lock or a page lock during the delete. ... you are expecting SQL Server to look at data that will all be ...
    (microsoft.public.sqlserver.server)
  • Re: SP1 does not work
    ... Perhaps you hit some other bug then the one referred to in SP1? ... > This mail is regarding a trouble I'm facing with SQL Server 2000. ... but at times I get a fatal SQL Server Exception when I ... > every time I go to the database with a new connection. ...
    (microsoft.public.sqlserver.programming)
  • Re: SP1 does not work
    ... Perhaps you hit some other bug then the one referred to in SP1? ... > This mail is regarding a trouble I'm facing with SQL Server 2000. ... but at times I get a fatal SQL Server Exception when I ... > every time I go to the database with a new connection. ...
    (microsoft.public.sqlserver.setup)
  • Re: Serious .NET Problem?
    ... Given their track record of compatibility, ... using ADO.NET to run T-SQL or a Stored Proc to help work around the bug? ... For help on SQL Server specific stuff, ...
    (borland.public.delphi.non-technical)
Loading