risposta-alla-domanda-sullo-sviluppo-web-bd.com

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
Non corretta sintassi 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 desc, in modo che l'utente con il risultato più grande sia in cima.

35
El Sa7eR

Non sono sicuro di cosa pensi che questo ORDER BY stia realizzando? Anche se fai metti ORDER BY nella vista in modo legale (ad es. Aggiungendo una clausola TOP), se selezioni solo dalla vista, ad es. 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, è possibile osservare che l'ordine funziona come previsto, ma questa è solo una coincidenza).

Per ottenere ciò che si desidera, è necessario aggiungere la clausola ORDER BY alle query che richiamano 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];

Il ORDER BY non ha 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:

enter image description here

Non esiste alcun operatore TOP e nessun ordinamento. SQL Server li ha ottimizzati completamente.

Ora, se cambi la vista per dire ORDER BY SalesID, ti capiterà semplicemente di ottenere l'ordinamento che la vista afferma, ma solo - come detto prima - per coincidenza.

Ma se cambi la tua query esterna per eseguire ORDER BY hai voluto:

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 ancora ottimizzato il TOP/ORDER BY nella vista, ma viene aggiunto un ordinamento (senza alcun costo, attenzione) per presentare i risultati ordinati da CustomerID:

enter image description here

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 prendere in considerazione l'aggiunta/modifica di un indice per supportarlo.

69
Aaron Bertrand

Ho avuto successo costringendo la vista a essere ordinata usando

SELECT TOP 9999999 ... ORDER BY something

Sfortunatamente l'uso di SELECT TOP 100 PERCENT non funziona a causa del problema here .

Da Sql 2012 è possibile 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 perché OFFSET impone la valutazione completa della valutazione anche se ulteriori join o filtri sulla 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.

2
Tom Deloford

Come uno dei commenti in questo post suggerisce di utilizzare stored procedure per restituire i dati ... Penso che sia la migliore risposta. Nel mio caso, ciò che ho fatto è stato scrivere un View per incapsulare la logica e le join della query, quindi ho scritto un Stored Proc per restituire i dati ordinati e il proc include anche altre funzionalità di miglioramento come i parametri per filtrare i dati. 

Ora devi scegliere di interrogare la vista, che ti consente di manipolare ulteriormente i dati. O hai la possibilità di eseguire il proc memorizzato, che è l'output più veloce e più preciso. 

STORED PROC Esecuzione per interrogare i dati

 exec [olap].[uspUsageStatsLogSessionsRollup]

VIEW Definition

USE [DBA]
GO

/****** Object:  View [olap].[vwUsageStatsLogSessionsRollup]    Script Date: 2/19/2019 10:10:06 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--USE DBA
-- select * from olap.UsageStatsLog_GCOP039 where CubeCommand='[ORDER_HISTORY]'
;

ALTER VIEW [olap].[vwUsageStatsLogSessionsRollup] as
(
    SELECT --*
        t1.UsageStatsLogDate
        , COALESCE(CAST(t1.UsageStatsLogDate AS nvarchar(100)), 'TOTAL- DATES:') AS UsageStatsLogDate_Totals
        , t1.ADUserNameDisplayNEW
        , COALESCE(t1.ADUserNameDisplayNEW, 'TOTAL- USERS:') AS ADUserNameDisplay_Totals
        , t1.CubeCommandNEW
        , COALESCE(t1.CubeCommandNEW, 'TOTAL- CUBES:') AS CubeCommand_Totals
        , t1.SessionsCount
        , t1.UsersCount
        , t1.CubesCount
    FROM
    (
        select 
            CAST(olapUSL.UsageStatsLogTime as date) as UsageStatsLogDate
            , olapUSL.ADUserNameDisplayNEW
            , olapUSL.CubeCommandNEW
            , count(*) SessionsCount
            , count(distinct olapUSL.ADUserNameDisplayNEW) UsersCount
            , count(distinct olapUSL.CubeCommandNEW) CubesCount
        from 
            olap.vwUsageStatsLog olapUSL
        where CubeCommandNEW != '[]'
        GROUP BY CUBE(CAST(olapUSL.UsageStatsLogTime as date), olapUSL.ADUserNameDisplayNEW, olapUSL.CubeCommandNEW )
            ----GROUP BY 
            ------GROUP BY GROUPING SETS
            --------GROUP BY ROLLUP
    ) t1

    --ORDER BY
    --  t1.UsageStatsLogDate DESC
    --  , t1.ADUserNameDisplayNEW
    --  , t1.CubeCommandNEW
)
;


GO

STORED PROC Definition

USE [DBA]
GO

/****** Object:  StoredProcedure [olap].[uspUsageStatsLogSessionsRollup]    Script Date: 2/19/2019 9:39:31 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      BRIAN LOFTON
-- Create date: 2/19/2019
-- Description: This proceedured returns data from a view with sorted results and an optional date range filter.
-- =============================================
ALTER PROCEDURE [olap].[uspUsageStatsLogSessionsRollup]
    -- Add the parameters for the stored procedure here
    @paramStartDate date = NULL,
    @paramEndDate date = NULL,
    @paramDateTotalExcluded as int = 0,
    @paramUserTotalExcluded as int = 0,
    @paramCubeTotalExcluded as int = 0
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @varStartDate as date 
        = CASE  
            WHEN @paramStartDate IS NULL THEN '1900-01-01' 
            ELSE @paramStartDate 
        END
    DECLARE @varEndDate as date 
        = CASE  
            WHEN @paramEndDate IS NULL THEN '2100-01-01' 
            ELSE @paramStartDate 
        END

    -- Return Data from this statement
    SELECT 
        t1.UsageStatsLogDate_Totals
        , t1.ADUserNameDisplay_Totals
        , t1.CubeCommand_Totals
        , t1.SessionsCount
        , t1.UsersCount
        , t1.CubesCount
        -- Fields with NULL in the totals
            --  , t1.CubeCommandNEW
            --  , t1.ADUserNameDisplayNEW
            --  , t1.UsageStatsLogDate
    FROM 
        olap.vwUsageStatsLogSessionsRollup t1
    WHERE

        (
            --t1.UsageStatsLogDate BETWEEN @varStartDate AND @varEndDate
            t1.UsageStatsLogDate BETWEEN '1900-01-01' AND '2100-01-01'
            OR t1.UsageStatsLogDate IS NULL
        )
        AND
        (
            @paramDateTotalExcluded=0
            OR (@paramDateTotalExcluded=1 AND UsageStatsLogDate_Totals NOT LIKE '%TOTAL-%')
        )
        AND
        (
            @paramDateTotalExcluded=0
            OR (@paramUserTotalExcluded=1 AND ADUserNameDisplay_Totals NOT LIKE '%TOTAL-%')
        )
        AND
        (
            @paramCubeTotalExcluded=0
            OR (@paramCubeTotalExcluded=1 AND CubeCommand_Totals NOT LIKE '%TOTAL-%')
        )
    ORDER BY
            t1.UsageStatsLogDate DESC
            , t1.ADUserNameDisplayNEW
            , t1.CubeCommandNEW

END


GO
1

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
0
Code.IT