I'm trying to create a view with an ORDER BY
clause. I have create it successfully on SQL Server 2012 SP1, but when I try to re-create it on SQL Server 2008 R2, I get this error:
Msg 102, Level 15, State 1, Procedure TopUsers, Line 11
Incorrect syntax near 'OFFSET'.
The code to create the view is
CREATE View [dbo].[TopUsersTest]
as
select
u.[DisplayName] , sum(a.AnswerMark) as Marks
From Users_Questions us inner join [dbo].[Users] u
on u.[UserID] = us.[UserID]
inner join [dbo].[Answers] a
on a.[AnswerID] = us.[AnswerID]
group by [DisplayName]
order by Marks desc
OFFSET 0 ROWS
=====================
This is a screen shot of the diagram
I wish to return users' DisplayName
and the UserTotalMarks
and order this result desc, so the user with the biggest result with be on the top.
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.SillyView
AS
SELECT TOP 100 PERCENT
SalesOrderID, OrderDate, CustomerID , AccountNumber, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY CustomerID;
GO
SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue
FROM dbo.SillyView;
Results:
SalesOrderID OrderDate CustomerID AccountNumber TotalDue
------------ ---------- ---------- -------------- ----------
43659 2005-07-01 29825 10-4020-000676 23153.2339
43660 2005-07-01 29672 10-4020-000117 1457.3288
43661 2005-07-01 29734 10-4020-000442 36865.8012
43662 2005-07-01 29994 10-4020-000227 32474.9324
43663 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, TotalDue
FROM dbo.SillyView
ORDER 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.989
51522 2007-07-22 11000 10-4030-011000 2587.8769
57418 2007-11-04 11000 10-4030-011000 2770.2682
51493 2007-07-20 11001 10-4030-011001 2674.0227
43767 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.