ORDER BY is undefined in SQL Server Views

Just found out (the hard way again, of course) that ORDER BY does not work with views, unless it is needed for selecting the TOP n records. As the documentation specifies:

“When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.”  (http://msdn.microsoft.com/en-us/library/ms188385.aspx)

For my code it meant that an .OrderBy() method had to be added to the LINQ that specified the data that was pulled from the view – in other words orderdering is never implicit.

Posted in SQL