Re: ORDER BY in VIEW not working

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

Relevant Pages

  • Re: DB Architecture Questions (for joe celko)
    ... So a deck of punch cards or a mag tape is just like an SQL Schema to ... a table has a name in the database. ... OCCURS clause. ... This rule would be enforce by a REFERENCES clause on the Orders table ...
    (microsoft.public.sqlserver.programming)
  • Re: Tricky Error
    ... someone could go ahead and inject their own SQL to create their own custom WHERE clause. ... If the cache is going to be big, I make this a seperate script and schedule it to be called daily, etc. ... it also sped up the application in the process as getting the contents of a specific file is quicker than connecting to a database or making a remote connection to fetch content. ...
    (comp.lang.php)
  • Re: cannot write to database query
    ... The select query is as shown below, I cannot change the information in the ... What are the SQL views of significant queries? ... What I failed to mention is that this database was fully functioning last ... It has a TRANSFORM clause. ...
    (microsoft.public.access.queries)
  • Re: Restoring a SQL 7 Back-up to SQL 2000
    ... restore the db in SQL2000 ... Note that A would work if you use the WITH REPLACE clause (Overwrite ... Jasper Smith (SQL Server MVP) ... I have a .bak of a database that was made by ...
    (microsoft.public.sqlserver.setup)
  • Re: CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to
    ... Go to the Database tab and click on the browse button next to the connection string. ... In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate. ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
Loading