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, ...
Get Inside Microsoft® SQL Server™ 2005 now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.