When working with SQL Server, you may encounter an error when attempting to create a view with an ORDER BY clause. In SQL Server 2005, the ORDER BY clause is not allowed in views. This limitation can be frustrating, but there are workarounds available to achieve the desired sorting in your queries.

The error message you may encounter is: “Msg 1033, Level 15, State 1 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.”

To resolve this issue, you have two methods:

Method 1: The Smart One

If you do not want to change all the queries that use the view, you can make the following changes to the view itself. This method ensures that the view returns the same result without significant performance loss.

Here’s an example:

CREATE VIEW vTestContactView AS
SELECT TOP 100 PERCENT FirstName, LastName
FROM Person.Contact
ORDER BY FirstName DESC

This approach works well in most cases, even when the ORDER BY clause uses a column that is not in the SELECT clause.

Method 2: The Technically Correct

If you only need to order by a column that is used in the SELECT clause of the view, you can use this method.

First, create the view without the ORDER BY clause:

CREATE VIEW vTestContactID AS
SELECT FirstName, LastName
FROM Person.Contact

Then, when querying the view, use the ORDER BY clause:

SELECT *
FROM vTestContactID
ORDER BY FirstName DESC

This method is technically correct and works well when you need to order by a column that is already included in the SELECT clause of the view.

By understanding these workarounds, you can overcome the limitation of not being able to use the ORDER BY clause in SQL Server views. Whether you choose the smart approach or the technically correct one, you can achieve the desired sorting in your queries.

Thank you for reading!