In SQL Server 2005, if you create a view utilizing the TOP (100) PERCENT and ORDER BY clauses, you will not get back your results in the order specified.
Example:
USE AdventureWorks
GO
CREATE VIEW CustomersOrderedbyTerritory
AS
SELECT TOP(100) PERCENT * FROM Sales.Customer
ORDER BY TerritoryID
GO
SELECT * FROM customersOrderedbyTerritory
You will notice that SELECTing from this view returns a result set 'ignorant' of the ORDER BY directive. In essence, the optimizer ignores the TOP and ORDER BY clauses. To achieve the desired result, there is a hot fix available from MSFT which introduces trace flag 168 and also requires you set the database compatibility level to 80 (SQL Server 2000).
Apparently, the problem also extends to Common Table Expressions (CTE) which use the same constructs such as :
WITH territorialcustomers AS
(
SELECT TOP(100) PERCENT * FROM Sales.Customer
ORDER BY TerritoryID
)
SELECT * FROM territorialcustomers
If you don't want to set your compatibility level to 80, you can use TOP (99.999999999999) instead. This somehow fools the optimizer to sort the data the way you want it to. Of course, it is only useful if your table contains less than 10 million records since the 0.000000000001% difference may start to show.
Well, if you insist on using the view, you can avoid the hot fix by using the ORDER BY clause in a table-valued function to retrieve your ordered data . You can then wrap your view around this UDF.
I have not checked if this behavior has been carried forward to SQL Server 2008.