Behaviour of ORDER BY inside view definition

One of the most common question I see in SQL forums relates to use of ORDER BY in a view and retrieval of data from view not maintaining the suggested order. This article aims at giving a clear picture on behavior of ORDER BY inside views for SQL versions from 2000.

In case of SQL 2005 and above,ORDER BY wont guarantee ordered result while used inside view definition.

This is clearly specified in books online for SQL 2005 & SQL 2008 as:-

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.

However the behaviour of ORDER BY was not the same in SQL 2000 and it used to correctly sort the data based on order specified in the definition. So my next effort was to reproduce this behaviour by changing the compatibility level of SQL 2005 db to 80. But to my surprise it still behaved as in case of SQL 2005. I investigated on cause for this and at last came across the below fix from Microsoft.

http://support.microsoft.com/kb/926292

This time when I applied this fix and tried the ORDER BY I was able to get the SQL 2000 behaviour on compatibility level 80. The behaviour for SQL 2005 was also preserved once you change compatibility level to 90.

So conclusion is if you're using SQL 2005 and above and want to get SQL 2000 behaviour for ORDER BY in compatibility level 80, you need to apply the above fix.