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

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

*/