Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?

I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express 2008 R2).

The basic idea is like this (simplified):

SELECT [EventType].Name, 
    [EventType].TotalOccurrences, 
    [Session].ID, 
    [Session].TotalOccurrences, 
    [Session].TotalOccurrences / [EventType].TotalOccurrences AS saturation
FROM [EventType]
    INNER JOIN [Session] ON [EventType].ID = [Session].Event
ORDER BY saturation

This view will never be called without a where clause though (which in my case would almost return the entire database). It will always be with WHERE [EventType].ID = x. However as soon as I add the where clause, the ORDER BY clause is ignored! Without the where clause it is working as expected.

Actually a different question but I'm throwing it in here while I'm at it, is there a better way to retrieve such info? The [EventType].Name and [EventType].TotalOccurrences will be identical for all returned rows, which is a small waist of network bandwidth. Not that that is a problem, but I'm wondering if anything exists to tackle that without the latency overhead of multiple round-trips to the database?

[Edit] The above sample was over-simplified, the calculated field has other factors so that it would return a different order than simply ORDER BY [Session].TotalOccurrences as it would in this example, but the principle should be clear.

[Edit2] From the answer I come to the conclusion that the Graphical Designer has more features than the underlying database:

The query you posted is not valid for creating a view; running CREATE VIEW xy AS for this query will result in an error. Are you using a TOP clause?

A view, being a table expression (a set), can't have the order defined, since that would be against the principles of a relational model (there is no order for rows in a relational table - a set is an unordered collection of tuples). Same goes for other table expressions - derived tables, CTEs etc.

From BOL article about the ORDER BY clause:


The ORDER BY clause is not valid in views, inline functions, derived
tables, and subqueries, unless either the TOP or OFFSET and FETCH
clauses are also specified. When ORDER BY is used in these objects,
the clause is used only to determine the rows returned by the TOP
clause or OFFSET and FETCH clauses. The ORDER BY clause does not
guarantee ordered results when these constructs are queried, unless
ORDER BY is also specified in the query itself.

Long story short: Use the ORDER BY clause in the outer query that references the view. Do not use it in a view. Even using it with TOP(100) PERCENT (or on SQL Server 2012, the OFFSET-FETCH equivalent) does not guarantee presentation order, it just means you'll get the top 100% of the rows, in any order.

StackExchange Database Administrators Q#66353, answer score: 11