Row Order on View Results

When I run a view on SQL 2005 the resulting rows are not in order, even if the SQL statement defining the view includes an order by clause.

Within the Microsoft SQL Manager Studio (SQL 2005), when the view is opened, the rows are in no specific order.

Records viewed remotely via ADO likewise are not displayed in order. Neither are records viewed via ODBC.

Interestingly, when opened in modify mode within the Microsoft SQL Manager Studio (SQL 2005), the view does display the records according to the ORDER BY clause.

On the other hand, the same view on SQL 2000 produces result sets organized according to the ORDER BY clause. This is true whether the view is opened normally or in design mode.

And records viewed remotely via ADO are displayed in order, as are records viewed via ODBC.

I find this disappointing and a stumbling block in moving databases out of SQL 2000 and into SQL 2005.

The Database that I used was one pulled into a SQL 2005 64 bit server out of a back up made by a SQL 2000 server of a SQL 2000 database.

In general it's not recommended to include an ORDER BY clause in a view. A view should define a new relation of attributes derived from existing attributes in the datamodel. A query using the view should apply an order by on the data represented by the view to produce an ordered resultset.