Creare una vista con la clausola ORDER BY

Sto cercando di creare una vista con una clausola ORDER BY . Lo ho creato correttamente su SQL Server 2012 SP1, ma quando provo a ricrearlo su SQL Server 2008 R2, ottengo questo errore:

Messaggio 102, livello 15, stato 1, procedura TopUsers, riga 11
Sintassi errata vicino a “OFFSET”.

Il codice per creare la vista è

 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 

=====================

Questa è una schermata del diagramma

Desidero restituire DisplayName degli utenti e UserTotalMarks e ordinare questo risultato, in modo che l’utente con il risultato più grande sia in cima.

Non sono sicuro di ciò che pensi che questo ORDER BY stia ottenendo? Anche se si inserisce ORDER BY nella vista in modo legale (ad esempio aggiungendo una clausola TOP ), se si seleziona solo dalla vista, ad esempio SELECT * FROM dbo.TopUsersTest; senza una clausola ORDER BY , SQL Server è libero di restituire le righe nel modo più efficiente, il che non corrisponde necessariamente all’ordine previsto. Questo perché ORDER BY è sovraccarico, nel senso che tenta di servire a due scopi: ordinare i risultati e dettare quali righe includere in TOP . In questo caso, TOP vince sempre (anche se, a seconda dell’indice scelto per la scansione dei dati, è ansible osservare che il tuo ordine funziona come previsto, ma questa è solo una coincidenza).

Per ottenere ciò che desideri, devi aggiungere la tua clausola ORDER BY alle query che estraggono i dati dalla vista, non al codice della vista stessa.

Quindi il tuo codice di visualizzazione dovrebbe essere solo:

 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]; 

L’ordine ORDER BY senso quindi non dovrebbe nemmeno essere incluso.


Per illustrare, utilizzando AdventureWorks2012, ecco un esempio:

 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; 

risultati:

 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 

E puoi vedere dal piano di esecuzione che TOP e ORDER BY sono stati assolutamente ignorati e ottimizzati da SQL Server:

inserisci la descrizione dell'immagine qui

Non esiste affatto un operatore TOP e nessun tipo. SQL Server li ha ottimizzati completamente.

Ora, se si modifica la vista per indicare ORDER BY SalesID , si otterrà l’ordinamento indicato dalla vista, ma solo – come accennato in precedenza – per coincidenza.

Ma se cambi la tua query esterna per eseguire l’ ORDER BY volevi:

 SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue FROM dbo.SillyView ORDER BY CustomerID; 

Ottieni i risultati ordinati nel modo desiderato:

 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 

E il piano ha comunque ottimizzato il TOP / ORDER BY nella vista, ma viene aggiunto un ordinamento (senza alcun costo, attenzione) per presentare i risultati ordinati da CustomerID :

inserisci la descrizione dell'immagine qui

Quindi, morale della storia, non mettere ORDER BY in vista. Inserisci ORDER BY nelle query che li fanno riferimento. E se l’ordinamento è costoso, potresti considerare l’aggiunta / modifica di un indice per supportarlo.

Ho avuto successo costringendo la vista a essere ordinata usando

 SELECT TOP 9999999 ... ORDER BY something 

Sfortunatamente l’utilizzo di SELECT TOP 100 PERCENT non funziona a causa del problema qui .

Il server SQL ci fornisce l’aggiornamento rapido in modo che possiamo creare la vista con ORDER BY

Ecco il link

Micorosft HotFix SQL Server

spero che possa essere d’aiuto

Da Sql 2012 è ansible forzare l’ordine nelle viste e sottoquery con OFFSET

 SELECT C.CustomerID, C.CustomerName, C.CustomerAge FROM dbo.Customer C ORDER BY CustomerAge OFFSET 0 ROWS; 

Attenzione: questa opzione deve essere utilizzata solo su elenchi di piccole dimensioni poiché OFFSET impone la valutazione completa della valutazione anche se ulteriori join o filtri nella vista ne riducono le dimensioni!

Non c’è un buon modo per forzare l’ordine in una vista senza un effetto collaterale, davvero e per una buona ragione.

L’errore è: FROM (SELECT empno,name FROM table1 where location = 'A' ORDER BY emp_no)

E la soluzione è: FROM (SELECT empno,name FROM table1 where location = 'A') ORDER BY emp_no

Si prega di provare la logica sottostante.

 SELECT TOP(SELECT COUNT(SNO) From MyTable) * FROM bar WITH(NOLOCK) ORDER BY SNO 

Basta usare TOP 100 per cento nel Select:

  CREATE VIEW [schema].[VIEWNAME] ( [COLUMN1], [COLUMN2], [COLUMN3], [COLUMN4]) AS SELECT TOP 100 PERCENT alias.[COLUMN1], alias.[COLUMN2], alias.[COLUMN3], alias.[COLUMN4] FROM [schema].[TABLENAME] AS alias ORDER BY alias.COLUMN1 GO 

Per aggiungere un ordine BY a una vista, eseguire quanto segue

 CREATE VIEW [dbo].[SQLSTANDARDS_PSHH] AS SELECT TOP 99999999999999 Column1, Column2 FROM dbo.Table Order by Column1 

utilizzare la procedura

Crea proc MyView come inizio SELEZIONA INIZIO 99999999999999 Colonna1, Colonna2 FROM dbo.Tabella Ordina per Colonna1 fine

eseguire la procedura

exec MyView