Creare viste indicizzate

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di 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 delle operazioni UPDATE, DELETE o INSERT (Data Manipulation Language, DML) 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.

Opzioni SET necessarie per le viste indicizzate

La valutazione della 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 obbligatorio Valore server predefinito Default

OLE DB e ODBC predefinito		 Default

DB-Library predefinito
ANSI_NULLS In... In... In... OFF
ANSI_PADDING In... In... In... OFF
ANSI_WARNINGS 1 In... In... In... OFF
ARITHABORT In... In... OFF OFF
CONCAT_NULL_YIELDS_NULL In... In... In... OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER In... In... In... 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 deterministico

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 per l'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

Oltre alle impostazioni delle opzioni SET e ai requisiti relativi alle funzioni deterministiche, è necessario che vengano soddisfatti i requisiti seguenti:

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

  • Quando si crea l'indice, l'opzione dell'indice IGNORE_DUP_KEY 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.

  • I riferimenti alle funzioni definite dall'utente nella vista devono essere specificati come nomi composti da due parti, ovvero <schema>.<funzione>.

  • 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à Nota
    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 Determinato impostando l'attributo DataAccess su DataAccessKind.None e l'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:

    Funzione Transact-SQL Possibili alternative
    COUNT Utilizzare COUNT_BIG
    Funzioni ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET e OPENXML)
    Media aritmetica AVG Usare COUNT_BIG e SUM come colonne separate
    Funzioni di aggregazione statistiche (STDEV, STDEVP, VAR e VARP)
    Funzione SUM che fa riferimento a un'espressione che ammette i valori Null Usare ISNULL all'interno di SUM() per far sì che l'espressione non ammetta i valori null
    Altre funzioni di aggregazione (MIN, MAX, CHECKSUM_AGG e STRING_AGG)
    Funzioni di aggregazione definite dall'utente (SQL CLR)
    Clausola SELECT Elemento Transact-SQL Possibile alternativa
    WITH cte AS Espressioni di tabella comuni (CTE) WITH
    SELECT Sottoquery
    SELECT SELECT [ <table>. ] * Assegnare un nome esplicito alle colonne
    SELECT SELECT DISTINCT Utilizzare GROUP BY
    SELECT SELECT TOP
    SELECT Clausola OVER, che include funzioni di rango o funzioni finestra di aggregazione
    FROM LEFT OUTER JOIN
    FROM RIGHT OUTER JOIN
    FROM FULL OUTER JOIN
    FROM OUTER APPLY
    FROM CROSS APPLY
    FROM Espressioni di tabella derivate (ovvero, usando SELECT nella clausola FROM)
    FROM Self-join
    FROM Variabili di tabella
    FROM Funzioni con valori di tabella inline
    FROM Funzioni con valori di tabella con più istruzioni
    FROM PIVOT, UNPIVOT
    FROM TABLESAMPLE
    FROM FOR SYSTEM_TIME Eseguire direttamente una query sulla tabella di cronologia temporale
    WHERE Predicati full-text (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
    GROUP BY Operatori CUBE, ROLLUP o GROUPING SETS Definire viste indicizzate separate per ogni combinazione di colonne GROUP BY
    GROUP BY HAVING
    Operatori Set UNION, UNION ALL, EXCEPT, INTERSECT Usare rispettivamente OR, AND NOT e AND nella clausola WHERE
    ORDER BY ORDER BY
    ORDER BY OFFSET
    Tipo di colonna di origine Possibile alternativa
    Tipi di colonna text, ntext e image con valore grande deprecati Eseguire la migrazione delle colonne rispettivamente a varchar(max), nvarchar(max) e varbinary(max).
    Colonne xml o FILESTREAM
    Colonne float1 nella chiave di indice
    Set di colonne di tipo sparse

    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.

consigli per datetime e 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 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'utilizzo di viste indicizzate nel motore di database, 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 poter creare una vista, un utente deve disporre dell'autorizzazione CREATE VIEW per il database e l'autorizzazione ALTER per lo schema in cui viene creata la vista. Se la tabella di base si trova all'interno di uno schema diverso, per la tabella è necessaria almeno l'autorizzazione REFERENCES. Se l'utente che crea l'indice è diverso dagli utenti che hanno creato la vista, per la creazione dell'indice è necessaria solo l'autorizzazione ALTER per la vista (coperta da ALTER nello schema).

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

Creare una vista indicizzata: esempio T-SQL

Nell'esempio seguente vengono creati una vista e un indice per tale vista 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

Le due query successive illustrano come usare la vista indicizzata, anche se la vista non è specificata nella clausola FROM.

--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 will also 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;

In questo esempio viene infine illustrata l'esecuzione di query direttamente dalla vista indicizzata. Prima di SQL Server 2016 (13.x) Service Pack 1, l'uso automatico di una vista indicizzata in Query Optimizer era supportato solo in edizioni specifiche di SQL Server. Nell'edizione SQL Server Standard è necessario usare l'hint per la query NOEXPAND per eseguire direttamente una query sulla vista indicizzata. A partire da SQL Server 2016 (13.x) Service Pack 1, tutte le edizioni supportano l'uso automatico di una vista indicizzata. Database SQL di Azure e Istanza gestita di SQL di Azure supportano l'uso automatico di viste indicizzate senza specificare l'hint NOEXPAND. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(datetime,'03/01/2012', 101)
    AND OrderDate < CONVERT(datetime,'04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(datetime,'03/01/2012', 101)
    AND OrderDate < CONVERT(datetime,'04/01/2012', 101)
ORDER BY OrderDate ASC;

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

Passaggi successivi