As I mentioned earlier, there is a reason behind disallowing an ORDER BY clause in the view’s query. A view is similar to a table in the sense that it represents a logical entity with no predetermined order to its rows–unlike a cursor that has order to its records.

Try running the following code, which attempts to introduce an ORDER BY clause in the VCustsWithOrders view:

ALTER VIEW dbo.VCustsWithOrders
AS

SELECT Country, CustomerID, CompanyName, ContactName, ContactTitle,
  Address, City, Region, PostalCode, Phone, Fax
FROM Customers AS C
WHERE EXISTS
  (SELECT * FROM dbo.Orders AS O
   WHERE O.CustomerID = C.CustomerID)
ORDER BY Country;
GO

The attempt fails, generating the following error:

Msg 1033, Level 15, State 1, Procedure VCustsWithOrders, Line 10
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.

Notice that the error doesn’t say that ORDER BY is disallowed altogether; rather, it says that there are a couple of exceptions where it is allowed–when TOP or FOR XML is also specified. Remember that both TOP and FOR XML are T-SQL extensions, not standard SQL elements. TOP and ORDER BY or ORDER BY and FOR XML are part of the result set specification, whereas ORDER BY alone is not, and only specifies a detail of presentation. Hence, TOP and ORDER BY or ORDER BY and FOR XML are allowed in a view definition while ORDER BY alone is not.

If you need to return sorted data to the client, you can always specify an ORDER BY clause in the outer query against the view:

SELECT Country, CustomerID, CompanyName
FROM dbo.VCustsWithOrders
ORDER BY Country;

Allowing an ORDER BY clause in a query against the view makes sense because obviously the client expects to get a physical object back–a record set. And for the client, there is a reason for wanting the data sorted.

Note that when using the TOP option in an outer query, the ORDER BY clause serves two functions: one is to determine which rows to pick, and the second is to determine the order of the records in the result cursor. However, when used with the TOP option in a table expression (for example, in a view’s query), the ORDER BY clause serves only one function–determining which rows to pick. In such a case, the view still represents a valid table (a set). When querying the view, there’s no guarantee that the rows would be returned in any particular order unless the outer query against the view has an ORDER BY clause as well. When TOP is also specified, the ORDER BY clause is allowed within a view (or other table expressions) because it serves only a logical function and not a physical one. Understanding this detail can help you develop correct code and avoid using table expressions in ways they really weren’t designed to work.

For example, an attempt to create a "sorted" view is wrong to begin with because a view is a table and a table has no order to its rows. It was common in SQL Server 2000 for programmers who were after "sorted" views to exploit what would seem to be a loophole in the system. The loophole involved creating an absurd view in which you specify TOP 100 PERCENT and an ORDER BY clause, as in:

ALTER VIEW dbo.VCustsWithOrders
AS

SELECT TOP (100) PERCENT
  Country, CustomerID, CompanyName, ContactName, ContactTitle,
  Address, City, Region, PostalCode, Phone, Fax
FROM Customers AS C
WHERE EXISTS
  (SELECT * FROM dbo.Orders AS O
   WHERE O.CustomerID = C.CustomerID)
ORDER BY Country;
GO

Note

The preceding code assumes you are using SQL Server 2005. Therefore, the use of a semicolon to terminate the ALTER VIEW statement, and the use of parentheses with the TOP option. If you want to test this code in SQL Server 2000, remove the parentheses and the semicolon.

So what is the meaning of the ORDER BY clause in the view’s query? Things are fuzzy here because the TOP option is not standard. But if you try to think in terms of sets, the ORDER BY clause is meaningless because you’re selecting all rows that meet the filter expression. When querying the view, SQL Server does not have to guarantee any order of the output unless the outer query has an ORDER BY clause. SQL Server 2005’s Books Online now has a helpful statement describing this behavior: "The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself."

Even if the optimizer does not ignore the ORDER BY clause and returns the data sorted, you shouldn’t rely on this behavior. Interestingly, when I ran the following query in SQL Server 2000, I got the data sorted as shown in Table 5-1:

SELECT Country, CustomerID, CompanyName
FROM dbo.VCustsWithOrders;

However, when I ran the query in SQL Server 2005, I got the unsorted output shown in Table 5-2.

Examining the execution plans in both versions explains what happened. Figure 5-1 shows the execution plan I got in SQL Server 2000, and Figure 5-2 shows the execution plan in SQL Server 2005.

Figure 5-1. Execution plan for a query against a view with ORDER BY in SQL Server 2000

Figure 5-2. Execution plan for a query against a view with ORDER BY in SQL Server 2005

You can see that the plan in SQL Server 2000 uses a sort operator, sorting the data by Country. On the other hand, SQL Server 2005’s optimizer completely ignored the combination of TOP (100) PERCENT and the ORDER BY clause. The optimizer realized that TOP and ORDER BY are meaningless here; therefore, it didn’t bother to sort the data. Unfortunately, programmers accustomed to the SQL Server 2000 behavior will consider this change in behavior a bug, even though the whole premise for creating such a view is wrong.

Note

The view designer in SQL Server 2000’s Enterprise Manager had a place to specify the order of a view, yielding a view definition with a TOP 100 PERCENT specification and an ORDER by clause. This might have been how programmers discovered this loophole. Unfortunately, the SQL Server Management Studio (SSMS) view designer in SQL Server 2005 also has a place to specify the order of a view. Although the SQL Server 2005 query processor may treat TOP 100 PERCENT .. ORDER BY differently from how the SQL Server 2000 processor did, this unfortunate usage will probably continue, because the SSMS view designer encourages it. It will simply create more confusion. Hopefully, you will realize that such use is absurd and refrain from it altogether.

When you’re done, run the following code to drop the VCustsWithOrders view:

IF OBJECT_ID('dbo.VCustsWithOrders') IS NOT NULL
  DROP VIEW dbo.VCustsWithOrders;