TOP 100 PERCENT and ORDER BY

In SQL Server 2008, the TOP 100 PERCENT and ORDER BY is ignored. In SQL Server 2005, the output order isn’t guaranteed.

For example if you created a view like so:

CREATE VIEW vwProduct
AS
SELECT TOP 100 PERCENT lProductID, vsProduct FROM dbo.tblProduct AS tl
ORDER BY lProductID DESC
GO

And tried to select from it expecting the results to be ordered as specified in the view, it’s not going to happen.

-- Select From View
SELECT lProductID, vsProduct
FROM vwProduct

This would return the results ordered ascending not descending:
lProductId  vsProduct
———– ———
1           Widget A
2           Widget B
3           Widget C
4           Widget D
5           Widget E