Create a view with ORDER BY clause

I'm not sure what you think this ORDER BY is accomplishing? Even if you do put ORDER BY in the view in a legal way (e.g. by adding a TOP clause), if you just select from the view, e.g. SELECT * FROM dbo.TopUsersTest; without an ORDER BY clause, SQL Server is free to return the rows in the most efficient way, which won't necessarily match the order you expect. This is because ORDER BY is overloaded, in that it tries to serve two purposes: to sort the results and to dictate which rows to include in TOP. In this case, TOP always wins (though depending on the index chosen to scan the data, you might observe that your order is working as expected - but this is just a coincidence).

In order to accomplish what you want, you need to add your ORDER BY clause to the queries that pull data from the view, not to the code of the view itself.

So your view code should just be:

CREATE VIEW [dbo].[TopUsersTest] AS   SELECT     u.[DisplayName], SUM(a.AnswerMark) AS Marks  FROM    dbo.Users_Questions AS uq    INNER JOIN [dbo].[Users] AS u      ON u.[UserID] = us.[UserID]     INNER JOIN [dbo].[Answers] AS a      ON a.[AnswerID] = uq.[AnswerID]    GROUP BY u.[DisplayName];

The ORDER BY is meaningless so should not even be included.

To illustrate, using AdventureWorks2012, here is an example:

CREATE VIEW dbo.SillyViewAS  SELECT TOP 100 PERCENT     SalesOrderID, OrderDate, CustomerID , AccountNumber, TotalDue  FROM Sales.SalesOrderHeader  ORDER BY CustomerID;GOSELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDueFROM dbo.SillyView;

Results:

SalesOrderID   OrderDate   CustomerID   AccountNumber   TotalDue------------   ----------  ----------   --------------  ----------43659          2005-07-01  29825        10-4020-000676  23153.233943660          2005-07-01  29672        10-4020-000117  1457.328843661          2005-07-01  29734        10-4020-000442  36865.801243662          2005-07-01  29994        10-4020-000227  32474.932443663          2005-07-01  29565        10-4020-000510  472.3108

And you can see from the execution plan that the TOP and ORDER BY have been absolutely ignored and optimized away by SQL Server:

There is no TOP operator at all, and no sort. SQL Server has optimized them away completely.

Now, if you change the view to say ORDER BY SalesID, you will then just happen to get the ordering that the view states, but only - as mentioned before - by coincidence.

But if you change your outer query to perform the ORDER BY you wanted:

SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDueFROM dbo.SillyViewORDER BY CustomerID;

You get the results ordered the way you want:

SalesOrderID   OrderDate   CustomerID   AccountNumber   TotalDue------------   ----------  ----------   --------------  ----------43793          2005-07-22  11000        10-4030-011000  3756.98951522          2007-07-22  11000        10-4030-011000  2587.876957418          2007-11-04  11000        10-4030-011000  2770.268251493          2007-07-20  11001        10-4030-011001  2674.022743767          2005-07-18  11001        10-4030-011001  3729.364

And the plan still has optimized away the TOP/ORDER BY in the view, but a sort is added (at no small cost, mind you) to present the results ordered by CustomerID:

So, moral of the story, do not put ORDER BY in views. Put ORDER BY in the queries that reference them. And if the sorting is expensive, you might consider adding/changing an index to support it.

I've had success forcing the view to be ordered using

SELECT TOP 9999999 ... ORDER BY something

Unfortunately using SELECT TOP 100 PERCENT does not work due the issue here.

From Sql 2012 you can force ordering in views and subqueries with OFFSET

SELECT      C.CustomerID,            C.CustomerName,            C.CustomerAgeFROM        dbo.Customer CORDER BY    CustomerAge OFFSET 0 ROWS;

Warning: this should only be used on small lists because OFFSET forces the full view to be evaluated even if further joins or filters on the view reduce its size!

There is no good way to force ordering in a view without a side effect really and for good reason.