Posted by Razvan Socol on 10/01/17 11:39

Hello, Ian

The ORDER BY clause cannot be used in a view if the TOP clause is not
specified. However, if we specify TOP 100 PERCENT and ORDER BY in a
view, this does not guarantee that the result of a "SELECT * FROM
TheView" will be ordered. Incidentally, it was ordered in SQL Server
2000, but it is not ordered in SQL Server 2005. It was never guaranteed
to work, it was just a side-effect that it worked in SQL Server 2000.
The ORDER BY clause in a view is taken into consideration only if the
TOP clause contains something other than "100 PERCENT", for example
"TOP 5" or "TOP 10 PERCENT".

Conclusion: You should not use ORDER BY in a view. You should put the
ORDER BY clause in the SELECT query that uses the view, for example
"SELECT * FROM TheView ORDER BY SomeColumn".

Razvan

[Back to original message]