Understanding Ambiguity of ORDER BY in SQL Server

Sometimes the ORDER BY clause doesn’t work exactly as you might expect, and it isn’t always obvious why you aren’t seeing the results you expected. In this article by Klaus Aschenbrenner, you get a great example as to why ORDER BY might not work as you expect.

Let’s start with a very simple SELECT statement.

Transact-SQL
-- A very simple SELECT statement
SELECT * FROM Person.Person
ORDER BY LastName
GO

As you can see from the previous listing, we just want to return the rows from the table Person.Person ordered by the column LastName. And because we want to be able to reuse that SQL statement as easily as possible, we finally put it into a view definition, like the following one.

Transact-SQL
-- This doesn't work
CREATE VIEW v_Persons
AS
SELECT * FROM Person.Person
ORDER BY LastName
GO

But as you can see, SQL Server is not able to create that view, and just returns an error message:

Msg 1033, Level 15, State 1, Procedure v_Persons, Line 27 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

The error message tells you that an ORDER BY clause is not allowed in a view when you don’t use a TOP, OFFSET or FOR XML expression. Based on that information in the error message, we can fix the problem very easily by adding a TOP 100 PERCENT clause into the view definition: we just return everything from the table, and we are finally allowed to use the ORDER BY in the view definition.