Posted by David Portas on 03/21/06 12:59

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/ms130214(en-US,SQL.90).aspx
--

[Back to original message]