What are the alternatives for an ORDER BY clause in a View?

This question just had to be in this site :)

ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view.

I know that there are ways of bypassing this limitation, for example TOP 99.999999 PERCENT, but I would like to know what is the best practice, not how to hack it.

So, if I want to create views in my database for personal use, meaning I want to connect to the database and just see the data fixed and sorted, how should I do it if I can't order a view?

Currently in my SQL Server DB I have views with the TOP hack, and I'm using them a lot, but it feels wrong.

Only the outermost ORDER BY will guarantee order

• Any intermediate or internal ORDER BY is ignored.

This includes ORDER BY in a view

• There is no implied order in any table

• There is no implied order from any index (clustered or not) on that table

Links

• "Sorting Rows with ORDER BY" (MSDN)

ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query. For example, consider the following view definition: (and example follow that matches this question)

StackExchange Database Administrators Q#4007, answer score: 9