When working with SQL Server, you may come across a common error message when using the ORDER BY clause in views. The error message states: “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.”

This error message suggests that in order to use the ORDER BY clause in a view, you need to include the TOP keyword as well. However, it is important to note that including the ORDER BY clause in a view is not recommended and can lead to unexpected behavior.

Typically, the purpose of a view is to provide a simplified and organized representation of data from one or more tables. The ordering of the data should be handled outside of the view, in the SELECT statement that queries the view.

In the past, it was common for developers to include the ORDER BY clause within the view itself, in order to save typing and make the view return a pre-sorted result set. Some developers even used the TOP 100 PERCENT keyword along with ORDER BY, assuming that this would simulate a SELECT statement with ORDER BY.

However, in SQL Server 2008, this behavior was corrected. When using TOP 100 PERCENT along with ORDER BY in a view, the ORDER BY clause is ignored. This means that the result set returned by the view will not be sorted according to the ORDER BY clause specified within the view.

Let’s take a look at a quick script to demonstrate this behavior:

USE AdventureWorks;

-- SELECT from Table Without TOP or ORDER BY
SELECT * FROM HumanResources.Shift;

-- SELECT from Table With TOP and ORDER BY DESC
SELECT TOP 100 PERCENT * FROM HumanResources.Shift ORDER BY ShiftID DESC;

-- Create View with same as above statement
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vwShift]'))
    DROP VIEW [dbo].[vwShift];

CREATE VIEW vwShift AS
SELECT TOP 100 PERCENT * FROM HumanResources.Shift ORDER BY ShiftID DESC;

-- Select From View
SELECT * FROM vwShift;

As you can see from the result of the script, the ORDER BY clause is ignored within the view, and the result set is returned as if the ORDER BY clause was not specified. It is important to note that SQL Server 2008 does not throw an error when creating a view with an ORDER BY clause; instead, it simply ignores the presence of the ORDER BY clause.

In conclusion, it is best practice to avoid using the ORDER BY clause within views in SQL Server. Instead, handle the ordering of the data outside of the view, in the SELECT statement that queries the view. This will ensure consistent and expected results when working with views in your SQL Server database.

If you have any similar ideas or experiences with using the ORDER BY clause in SQL Server views, please feel free to leave a comment below.