Over the weekend I migrated various databases to SQL Server 2005 only to find that the hierarchical menus on the websites using the databases were incomplete and out of order.

After a few hours of head scratching I discovered some views along the lines of -

SELECT TOP 100 PERCENT col1, col2, col3
FROM sometable
ORDER BY col1, col2

The ORDER BY clause should only be used in views under certain conditions i.e. when using the TOP operator and the above view worked just fine under SQL Server 2000.

However in SQL Server 2005, the query optimiser optimises out TOP 100 PERCENT because it recognises that no rows are being eliminated from the resultset and because of this optimisation it also optimises out the the ORDER BY clause.