Inside Microsoft® SQL Server™ 2005

ORDER BY in a View

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, ...