Crear una vista con la cláusula ORDER BY
Estoy intentando crear una vista con una cláusula ORDER BY
. He creado con éxito en SQL Server 2012 SP1, pero cuando trato de volver a crear en SQL Server 2008 R2, me sale este error:
Msg 102, Level 15, State 1, Procedure TopUsers, Line 11
Incorrect sintaxis cerca de 'OFFSET'.
El código para crear la vista es
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
=====================
Esta es una captura de pantalla del diagrama
Deseo devolver el DisplayName
de los usuarios y el UserTotalMarks
y ordenar este resultado de forma descendente, para que el usuario con el mayor resultado esté en la parte superior.
38
3
No estoy seguro de qué crees que está consiguiendo este
ORDER BY
. Incluso si usted hace ponerORDER BY
en la vista de una manera legal (por ejemplo, mediante la adición de una cláusulaTOP
), si sólo selecciona de la vista, por ejemplo,SELECT * FROM dbo.TopUsersTest;
sin una cláusulaORDER BY
, SQL Server es libre de devolver las filas de la manera más eficiente, que no coincidirá necesariamente con el orden que usted espera. Esto se debe a que la cláusulaORDER BY
está sobrecargada, ya que intenta servir a dos propósitos: ordenar los resultados y dictar qué filas incluir enTOP
. En este caso,TOP
siempre gana (aunque dependiendo del índice elegido para escanear los datos, podría observar que su orden funciona como se esperaba - pero esto es sólo una coincidencia).**Para conseguir lo que quieres, necesitas añadir tu cláusula
ORDER BY
a las consultas que extraen los datos de la vista, no al código de la propia vista.Así que el código de la vista debería ser:
El
ORDER BY
no tiene sentido, por lo que ni siquiera debería incluirse.Para ilustrar, usando AdventureWorks2012, aquí está un ejemplo:
Resultados:
Y se puede ver en el plan de ejecución que el
TOP
yORDER BY
han sido absolutamente ignorados y optimizados por SQL Server:No hay operador
TOP
en absoluto, y no hay ordenación. SQL Server los ha optimizado completamente.Ahora, si cambias la vista a
ORDER BY SalesID
, obtendrás el orden que la vista indica, pero sólo - como se mencionó antes - por coincidencia.Pero si cambias tu consulta externa para realizar el
ORDER BY
que querías:Obtendrá los resultados ordenados de la forma que desee:
Y el plan aún ha optimizado el
TOP
/ORDER BY
en la vista, pero se añade una ordenación (con un coste no pequeño, eso sí) para presentar los resultados ordenados porCustomerID
:Así que, moraleja de la historia, no pongas ORDER BY en las vistas. Pon ORDER BY en las consultas que hacen referencia a ellas. Y si la ordenación es costosa, podrías considerar añadir/cambiar un índice para soportarla.
He tenido éxito forzando la vista a ser ordenada usando
Desafortunadamente usando
SELECT TOP 100 PERCENT
no funciona debido al problema aquí.El error es:
FROM (SELECT empno,name FROM table1 where location = 'A' ORDER BY emp_no)
La solución es:
FROM (SELECT empno,name FROM table1 where location = 'A') ORDER BY emp_no
.