My Microsoft SQL Server VIEW create statement has a TOP 100 PERCENT in the SELECT clause. Why would you use SELECT TOP 100 PERCENT in a SQL Server 2000 query?
A VIEW in Microsoft SQL Server will not allow you to have an ORDER BY statement. However, if you put a TOP 100 PERCENT in the SELECT statement, SQL Server will allow the ORDER BY.
Details can be found here:
http://techrepublic.com.com/5208-9592-0.html?forumID=88&threadID=165375&messageID=2077343
I came across this while editing some VIEWS for a co-worker. He created the views using SQL Server’s visual tools. The tools automatically add this piece of code to the top of the CREATE statement for the views.
Even if you put an order by in your view it is used ONLY to determine the set of data for the TOP keyword, and because TOP 100 PERCENT is the entire file, the order by is optimised out.
The order of data returned from a view cannot be guaranteed by an order by in the view, you must order the data yourself.
See: http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx