SQL 2005 : Using ORDER BY in a view.

The ORDER BY clause is not allowed in a view. It is ignored by the query parser.

To get round this prior to SQL 2005, a TOP 100 PERCENT predicate can be specified in the view definition, e.g. -

CREATE VIEW vw_AlphabeticalEmployees AS 
SELECT TOP 100 PERCENT 
            Contact.LastName, 
            Contact.FirstName, 
            Employee.Title 
    FROM Person.Contact Contact 
            INNER JOIN HumanResources.Employee Employee 
            ON Contact.ContactID = Employee.ContactID 
    ORDER BY LastName, FirstName   

This no longer works in SQL 2005. The view returns data, but the ordering is not applied.

To get round this we can specify TOP 2147483647.
2147483647 is the largest integer that can be passed to the statement and should safely cover most OLTP recordsets!

The SQL 2005 version is therefore -

ALTER VIEW vw_AlphabeticalEmployees AS 
SELECT TOP 2147483647 
            Contact.LastName, 
            Contact.FirstName, 
            Employee.Title 
    FROM Person.Contact Contact 
            INNER JOIN HumanResources.Employee Employee 
            ON Contact.ContactID = Employee.ContactID 
    ORDER BY LastName, FirstName   

20/06/2007 - A colleague has just alerted me to the fact that this feature has now been addressed by a hotfix http://support.microsoft.com/kb/926292

OakLeaf Systems: SQL Server 2005 Ordered View and Inline Function Problems