The T-SQL ORDER BY clause is not allowed in view definition unless it includes the TOP, FOR XML or ROW_NUMBER() expressions.

When an ORDER BY create view attempted, following is the error message:

/* Msg 1033, Level 15, State 1, Procedure ww, Line 3
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.
*/

The following Microsoft SQL Server T-SQL example scripts illustrate view definition with ORDER BY and usage. Note that only the ORDER BY clause in the outer SELECT query guarantees the desired sorting.

-- MSSQL create view with order by - T-SQL row number over order by
-- SQL inner join - left outer join - left join
USE AdventureWorks2008;
GO
CREATE VIEW vProductDetail
AS
  SELECT ROW_NUMBER()
           OVER(ORDER BY P.[Name])  AS RowID,
         P.[Name]                   AS ProductName,
         PM.[Name]                  AS ProductModel,
         PC.[Name]                  AS ProductCategory,
         PS.[Name]                  AS ProductSubCategory,
         PD.[DESCRIPTION],
         P.ProductNumber,
         COALESCE(P.Color,'')       AS Color,
         COALESCE(P.[Size],'')      AS [Size],
         COALESCE(P.[Weight],0.0)   AS [Weight],
         P.StandardCost,
         COALESCE(P.Style,'')       AS  Style,
         COALESCE(P.Class,'')       AS Class,
         P.ListPrice
  FROM   Production.Product P
         INNER JOIN Production.ProductSubcategory PS
         INNER JOIN Production.ProductCategory PC
           ON PS.ProductCategoryID = PC.ProductCategoryID
           ON P.ProductSubcategoryID = PS.ProductSubcategoryID
         INNER JOIN Production.ProductProductPhoto PPP
           ON P.ProductID = PPP.ProductID
         INNER JOIN Production.ProductPhoto PP
           ON PPP.ProductPhotoID = PP.ProductPhotoID
         LEFT OUTER JOIN Production.ProductDescription PD
         INNER JOIN Production.ProductModel PM
         INNER JOIN Production.ProductModelProductDescriptionCulture PCULT
           ON PM.ProductModelID = PCULT.ProductModelID
           ON PD.ProductDescriptionID = PCULT.ProductDescriptionID
           ON P.ProductModelID = PM.ProductModelID
  WHERE  (PCULT.CultureID = 'en')
GO
 
-- SQL view select results may be unordered despite the ordering in the view
SELECT * FROM   vProductDetail
GO
 
-- SQL view select results are ordered
SELECT * FROM   vProductDetail
ORDER BY RowID
GO
 
/* Partial results
 

*/