How to get an ordered view?

My 'VIEW' is ordered. But when I use a select to show its result, the output is shuffled. This behavior also happens with the following statement

SELECT * FROM (SELECT * FROM TABLE ORDER BY COLUMNA) AS DERIVEDTABLE

How to prevent this shuffling?

Unfortunately, views can't be ordered in SQL Server. You have to do it on the select statement reading the view.

Lame, I know.

you must only have an ORDER BY on the outermost select that uses the view:

SELECT * FROM (SELECT * FROM TABLE) AS DERIVEDTABLE ORDER BY YourColumn

unless you use TOP

SELECT * FROM (SELECT top 1000000 * FROM TABLE ORDER BY COLUMNA) AS DERIVEDTABLE

however the final sort order could still change depending on the outer query, unless you add another order by:

SELECT * FROM (SELECT top 1000000 * FROM TABLE ORDER BY COLUMNA) AS DERIVEDTABLE
ORDER BY COLUMNA

You cannot. The only way to specify an order is to ask for it at the outermost level of the query - that's the only place where ORDER BY is intended to order the final result set. Any other use of ORDER BY is only intended to assist with other operations (such as to define what the "TOP 10" are when using TOP).

SQL Server 2000 used to be able to be tricked into applying an ORDER BY in a view, and unfortunately, the view designers in Enterprise Manager and SSMS continue to pretend that this works. It doesn't. By not allowing the trick to work any longer, this presumably offers more opportunities for query optimization.