Creazione di viste indicizzate

Si applica a: sìSQL Server (tutte le versioni supportate) Sìdatabase SQL di Azure

Questo articolo descrive come creare indici in una vista. Il primo indice creato per una vista deve essere un indice cluster univoco. Dopo aver creato l'indice cluster univoco, è possibile creare più indici non cluster. La creazione di un indice cluster univoco per una vista consente un miglioramento delle prestazioni delle query, in quanto la vista viene archiviata nel database in modo analogo a una tabella con un indice cluster. Le viste indicizzate possono essere usate da Query Optimizer per velocizzare l'esecuzione delle query. Non è necessario fare riferimento alla vista nella query affinché venga usata da Query Optimizer per una sostituzione.

Passaggi

Per la creazione e la corretta implementazione di una vista indicizzata, è fondamentale effettuare le operazioni seguenti:

  1. Verificare che le opzioni SET siano corrette per tutte le tabelle esistenti a cui verrà fatto riferimento nella vista.
  2. Verificare che le opzioni SET della sessione siano impostate in modo corretto prima di creare qualsiasi tabella e la vista.
  3. Verificare che la definizione della vista sia deterministica.
  4. Verificare che la tabella di base abbia lo stesso proprietario della vista.
  5. Creare la vista usando l'opzione WITH SCHEMABINDING.
  6. Creare l'indice cluster univoco per la vista.

Importante

Durante l'esecuzione di DML1 in una tabella cui viene fatto riferimento da un numero elevato di viste indicizzate o da un numero minore di viste indicizzate molto complesse, è necessario aggiornare anche le viste indicizzate cui viene fatto riferimento. Di conseguenza, è possibile che le prestazioni delle query DML si riducano notevolmente o, in alcuni casi, non venga prodotto un piano di query. In questi scenari, testare le query DML prima dell'uso in produzione, analizzare il piano di query e ottimizzare/semplificare l'istruzione DML.

1 Ad esempio, operazioni UPDATE, DELETE o INSERT.

Opzioni SET necessarie per le viste indicizzate

La valutazione di una stessa espressione può produrre risultati diversi nel Motore di database se sono attive diverse opzioni SET quando la query viene eseguita. Ad esempio, dopo aver impostato l'opzione SET CONCAT_NULL_YIELDS_NULL su ON, l'espressione 'abc' + NULL restituisce il valore NULL. Tuttavia, dopo aver impostato CONCAT_NULL_YIELDS_NULL su OFF, la stessa espressione produce 'abc'.

Per essere certi che le viste possano essere gestite in modo corretto e restituiscano risultati coerenti, è necessario usare valori fissi per varie opzioni SET delle viste indicizzate. Le opzioni SET specificate nella tabella seguente devono essere impostate sui valori indicati nella colonna Valore obbligatorio quando si verificano le seguenti condizioni:

  • Vengono creati la vista e gli indici successivi nella vista.
  • Le tabelle di base a cui si fa riferimento nella vista quando viene creata la vista stessa.
  • Quando viene eseguita un'operazione di inserimento, aggiornamento o eliminazione su una qualsiasi tabella usata nella vista indicizzata, incluse operazioni quali la copia bulk, la replica e le query distribuite.
  • Quando la vista indicizzata viene usata in Query Optimizer per generare il piano di query.
Opzioni SET Valore richiesto Valore server predefinito Predefinito

OLE DB e ODBC predefinito
Predefinito

DB-Library predefinito
ANSI_NULLS ON ON ON OFF
ANSI_PADDING ON ON ON OFF
ANSI_WARNINGS1 ON ON ON OFF
ARITHABORT ON ON OFF OFF
CONCAT_NULL_YIELDS_NULL ON ON ON OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ON ON ON OFF
         

1 L'impostazione di ANSI_WARNINGS su ON imposta in modo implicito ARITHABORT su ON.

Se si usa una connessione server OLE DB o ODBC, l'unico valore da modificare è l'impostazione ARITHABORT. Tutti i valori DB-Library devono essere impostati in modo corretto a livello di server tramite sp_configure oppure dall'applicazione tramite il comando SET.

Importante

È consigliabile impostare l'opzione utente ARITHABORT su ON per l'intero server immediatamente dopo la creazione della prima vista indicizzata o del primo indice in una colonna calcolata in qualsiasi database del server.

Requisito di visualizzazione deterministica

La definizione di una vista indicizzata deve essere deterministica. Una vista è deterministica se tutte le espressioni nell'elenco di selezione, nonché nelle clausole WHERE e GROUP BY, sono deterministiche. Le espressioni deterministiche restituiscono sempre lo stesso risultato ogni volta che vengono valutate con un set specifico di valori di input. Nelle espressioni deterministiche è possibile usare solo funzioni deterministiche. La funzione DATEADD, ad esempio, è deterministica perché restituisce sempre lo stesso risultato per un dato set di valori dei relativi tre parametri. GETDATE non è deterministica perché viene sempre richiamata con lo stesso argomento, ma il valore restituito cambia ogni volta che viene eseguita.

Per determinare se una colonna della vista è deterministica, usare la proprietà IsDeterministic della funzione COLUMNPROPERTY . Usare la proprietà IsPrecise della funzione COLUMNPROPERTY per determinare se una colonna deterministica in una vista con associazione di schema è precisa. COLUMNPROPERTY restituisce 1 se TRUE, 0 se FALSE e NULL se il valore di input non è valido. Questo significa che la colonna non è deterministica o non è precisa.

Se in un'espressione deterministica sono contenute espressioni float, il risultato esatto può dipendere dall'architettura del processore o dalla versione del microcodice. Per garantire l'integrità dei dati, le espressioni di questo tipo possono essere usate solo come colonne non chiave delle viste indicizzate. Le espressioni deterministiche che non contengono espressioni float sono definite precise. Nelle colonne chiave e nelle clausole WHERE o GROUP BY delle viste indicizzate è possibile usare solo espressioni deterministiche precise.

Requisiti aggiuntivi

Devono essere soddisfatti anche i requisiti seguenti, oltre alle opzioni SET e ai requisiti delle funzioni deterministiche

  • L'utente che esegue CREATE INDEX deve essere il proprietario della vista.

  • Quando si crea l'indice, l'opzione IGNORE_DUP_KEY dell'indice deve essere impostata su OFF (impostazione predefinita).

  • I riferimenti alle tabelle devono essere specificati come nomi composti da due parti, ovvero schema . nometabella , nella definizione della vista.

  • Le funzioni definite dall'utente a cui viene fatto riferimento nella vista devono essere create usando l'opzione WITH SCHEMABINDING.

  • A qualsiasi funzione definita dall'utente a cui si fa riferimento nella vista deve essere fatto riferimento usando nomi in due parti, <schema> . <function> .

  • La proprietà di accesso ai dati di una funzione definita dall'utente deve essere NO SQL e la proprietà di accesso esterno deve essere NO.

  • Le funzioni CLR (Common Language Runtime) possono essere incluse solo nell'elenco SELECT della vista ma non possono fare parte della definizione della chiave di indice cluster. Le funzioni CLR non possono essere incluse nella clausola WHERE della vista o nella clausola ON di un'operazione JOIN nella vista.

  • Le proprietà delle funzioni CLR e dei metodi di tipi CLR definiti dall'utente usati nella definizione della vista devono essere impostate come illustrato nella tabella seguente.

    Proprietà Note
    DETERMINISTIC = TRUE Deve essere dichiarata in modo esplicito come attributo del metodo di Microsoft .NET Framework.
    PRECISE = TRUE Deve essere dichiarata in modo esplicito come attributo del metodo di .NET Framework.
    DATA ACCESS = NO SQL Determinata dall'impostazione dell'attributo DataAccess su DataAccessKind.None e dell'attributo SystemDataAccess su SystemDataAccessKind.None.
    EXTERNAL ACCESS = NO Per le routine CLR il valore predefinito di questa proprietà è NO.
       
  • La vista deve essere creata usando l'opzione WITH SCHEMABINDING.

  • La vista deve contenere riferimenti solo a tabelle di base che si trovano nello stesso database della vista. La vista non può fare riferimento ad altre viste.

  • Se è presente GROUP BY, la definizione di VIEW deve contenere COUNT_BIG(*) e non deve contenere HAVING. Queste restrizioni di GROUP BY vengono applicate solo alla definizione della vista indicizzata. Una query può usare una vista indicizzata nel relativo piano di esecuzione anche se non soddisfa le restrizioni di GROUP BY.

  • Se la definizione della vista include una clausola GROUP BY, la chiave dell'indice cluster univoco può contenere riferimenti solo alle colonne specificate nella clausola GROUP BY.

  • Nell'istruzione SELECT della definizione della vista non possono essere contenuti gli elementi Transact-SQL seguenti:

    Elementi di Transact-SQL (continua) (continua)
    COUNT Funzioni ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET e OPENXML) Join OUTER (LEFT, RIGHT o FULL)
    Tabella derivata (definita specificando un'istruzione SELECT nella clausola FROM) Self-join Specifica di colonne tramite SELECT * o SELECT <table_name>.*
    DISTINCT STDEV, STDEVP, VAR, VARP o AVG Espressione di tabella comune (CTE)
    Colonne float1, text, ntext, image, XML o filestream Sottoquery Clausola OVER, che include funzioni di rango o funzioni finestra di aggregazione
    Predicati full-text (CONTAINS, FREETEXT) Funzione SUM che fa riferimento a un'espressione che ammette i valori Null ORDER BY
    Funzione di aggregazione CLR definita dall'utente TOP Operatori CUBE, ROLLUP o GROUPING SETS
    MIN, MAX Operatori UNION, EXCEPT o INTERSECT TABLESAMPLE
    Variabili di tabella OUTER APPLY o CROSS APPLY PIVOT, UNPIVOT
    Set di colonne di tipo sparse Funzione con valori di tabella inline o con istruzioni multiple OFFSET
    CHECKSUM_AGG STRING_AGG

    1 La vista indicizzata può contenere colonne di tipo float che, tuttavia, non possono essere incluse nella chiave di indice cluster.

    Importante

    Le viste indicizzate non sono supportate sulle query temporali, ovvero quelle che usano la clausola FOR SYSTEM_TIME.

Raccomandazioni relative a datetime/smalldatetime

Quando si fa riferimento a valori letterali stringa datetime e smalldatetime nelle viste indicizzate, è consigliabile convertire in modo esplicito il valore letterale nel tipo di dati desiderato usando uno stile del formato di data deterministico. Per un elenco degli stili del formato di data deterministici, vedere CAST e CONVERT (Transact-SQL). Per altre informazioni sulle espressioni deterministiche e non deterministiche, vedere la sezione Considerazioni in questa pagina.

Le espressioni che prevedono la conversione implicita di stringhe di caratteri nel tipo di dati datetime o smalldatetime sono considerate non deterministiche. Per altre informazioni, vedere Conversione non deterministica di stringhe di valori letterali in valori DATE.

Considerazioni sulle prestazioni con le viste indicizzate

Durante l'esecuzione di DML, ad esempio di UPDATE, DELETE o INSERT, in una tabella cui viene fatto riferimento da un numero elevato di viste indicizzate o da un numero minore di viste indicizzate molto complesse, è necessario aggiornare anche le viste indicizzate. Di conseguenza, è possibile che le prestazioni delle query DML si riducano notevolmente o, in alcuni casi, non venga prodotto un piano di query. In questi scenari, testare le query DML prima dell'uso in produzione, analizzare il piano di query e ottimizzare/semplificare l'istruzione DML.

Per impedire l'uso Motore di database di viste indicizzate, includere l'hint OPTION (EXPAND VIEWS) nella query. Inoltre, l'errata impostazione di una qualsiasi delle opzione elencate impedisce l'utilizzo degli indici delle viste in Query Optimizer. Per altre informazioni sull'hint OPTION (EXPAND VIEWS), vedere SELECT (Transact-SQL).

Varie considerazioni aggiuntive

  • L'impostazione dell'opzione large_value_types_out_of_row delle colonne di una vista indicizzata è ereditata dall'impostazione della colonna corrispondente nella tabella di base. Questo valore viene impostato mediante sp_tableoption. L'impostazione predefinita per le colonne generate da espressioni è 0. Ciò significa che i tipi per valori di grandi dimensioni vengono archiviati all'interno delle righe.

  • È possibile creare viste indicizzate per una tabella partizionata, nonché partizionare questo tipo di viste.

  • Tutti gli indici di una vista vengono eliminati con la rimozione della vista. Tutti gli indici non cluster e tutte le statistiche create automaticamente nella vista vengono eliminati con l'eliminazione dell'indice cluster. Le statistiche create dall'utente nella vista vengono conservate. È possibile eliminare gli indici non cluster singolarmente. L'eliminazione dell'indice cluster nella vista determina la rimozione del set di risultati archiviato e la vista tornerà a essere elaborata come standard da Query Optimizer.

  • È possibile disabilitare gli indici di tabelle e viste. Quando l'indice cluster di una tabella è disabilitato, anche gli indici delle viste associate alla tabella sono disabilitati.

Autorizzazioni

Per creare la vista, un utente deve disporre dell'autorizzazione CREATE VIEW nel database e dell'autorizzazione ALTER per lo schema in cui viene creata la vista. Se la tabella di base si trova all'interno di uno schema diverso, l'autorizzazione REFERENCES per la tabella è necessaria come minimo. Se l'utente che crea l'indice è diverso dagli utenti che hanno creato la vista, per la sola creazione dell'indice è necessaria l'autorizzazione ALTER per la vista (coperta da ALTER nello schema).

Nota

Gli indici possono essere creati solo nelle viste con lo stesso proprietario della tabella o delle tabelle a cui si fa riferimento. Si tratta anche di una catena di proprietà intatta tra la vista e le tabelle. In genere, quando la tabella e la vista si trovano all'interno dello stesso schema, lo stesso proprietario dello schema si applica a tutti gli oggetti all'interno dello schema. È quindi possibile creare una vista e non essere il proprietario della vista. D'altra parte è anche possibile che i singoli oggetti all'interno di uno schema hanno proprietari espliciti diversi. La colonna principal_id in sys.tables contiene un valore se il proprietario è diverso dal proprietario dello schema.

Creare una vista indicizzata: esempio di SQL T

Nell'esempio seguente vengono creati una vista e un indice per tale vista, quindi vengono incluse due query che usano la vista indicizzata nel database AdventureWorks.

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
   QUOTED_IDENTIFIER, ANSI_NULLS ON;
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
   DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
   WITH SCHEMABINDING
   AS  
      SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
   ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,
   OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o
   ON od.SalesOrderID=o.SalesOrderID
      AND o.OrderDate >= CONVERT(datetime,'05/01/2012',101)
WHERE od.ProductID BETWEEN 700 and 800
   GROUP BY OrderDate, ProductID
   ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o
   ON od.SalesOrderID=o.SalesOrderID
      AND o.OrderDate >= CONVERT(datetime,'03/01/2012',101)
      AND o.OrderDate < CONVERT(datetime,'04/01/2012',101)
    GROUP BY OrderDate
    ORDER BY OrderDate ASC;

Per altre informazioni, vedere CREATE VIEW (Transact-SQL).

Vedere anche