Guaranteeing order in views

In SQL Server 2005, using the TOP 100 PERCENT clause with an ORDER BY xxx doesn't gurantee that the results will be ordered by xxx. See this article for more info.

So what to do?

I believe that you can use the following syntax:

create view guaranteedOrderView
select xxx, row_number() over (order by xxx) as OrderNo
from exampleTable

NOTE: I've not tested this assumption. One of the extremely smart developers at my work told me that the optimizer might well... optimize... out this order.