ORDER BY in views - difference in 2005 vs 2000?

I am no SQL wizard, so if I have a flawed basic understanding please be
blunt so I will understand...
In 2000, we set up a lot of views with ORDER BY to get records in an
expected order. If I had ViewA and it contained an order by for Field1, then
if I did "SELECT * from ViewA" in SQL Server 2000, the records came back
sorted by Field1. In SQL Server 2005, it does not. If I get the definition
of the veiew and submit that as SQL, it works, but that seems like a
rediculous workaround as it costs an extra round trip and bypasses
precompilation. The documentation says "ORDER BY guarantees a sorted result
only for the outermost SELECT statement of a query."
Any help or suggestions are appreciated."Andy Barnhart" <andyDOTbarnhart@.ciincDOTcom> wrote in message
news:OyEX3Vc9FHA.1224@.TK2MSFTNGP12.phx.gbl...
> I am no SQL wizard, so if I have a flawed basic understanding please be
> blunt so I will understand...
> In 2000, we set up a lot of views with ORDER BY to get records in an
> expected order. If I had ViewA and it contained an order by for Field1,
> then if I did "SELECT * from ViewA" in SQL Server 2000, the records came
> back sorted by Field1. In SQL Server 2005, it does not. If I get the
> definition of the veiew and submit that as SQL, it works, but that seems
> like a rediculous workaround as it costs an extra round trip and bypasses
> precompilation. The documentation says "ORDER BY guarantees a sorted
> result only for the outermost SELECT statement of a query."
> Any help or suggestions are appreciated.
There have been a few posts lately on this.
Seems that SQL server has never guaranteed the order when using ORDER BY in
a view.
This works: SELECT TOP 5 ... FROM table ORDER BY
SQL guarantees that the correct TOP 5 will be returned, but does not
guarantee the order.
There was not an issue with SQL 2000 but there is with 2005.
The only suggestion that I can give you is: stop using ORDER BY in views
unless you want something like my example.
You should NEVER has used ORDER BY in views in the way you described...
sorry.
You should create a view with no ordering and then: Select ... from yourView
ORDER BY ...|||Andy Barnhart wrote:
> if I did "SELECT * from ViewA" in SQL Server 2000, the records came back
> sorted by Field1.
Maybe, maybe not. The resulting order of a SELECT statement without an
ORDER BY clause is always undefined - it's dangerous to assume any
predictable ordering.

> The documentation says "ORDER BY guarantees a sorted result
> only for the outermost SELECT statement of a query."
>
That's correct.

> Any help or suggestions are appreciated.
Try:
SELECT * FROM ViewA ORDER BY col
which works equally well in SQL Server 2000 and 2005. :-)
David Portas
SQL Server MVP
--