Published 20 November 2017
Usage of ORDER BY in view or single statement (inline) TVF
Views and inline Table-valued functions have no intrinsic order. The order of the result is specified only by the SELECT statement that specifies the View, iTVF or table.
If executed, this will trigger an error ‘The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified’.
Some programmers wrongly assume that adding TOP 100 PERCENT is ok because the error disappears.
You should include the ORDER BY only in the outermost query.