Creare viste indicizzate

Articolo
14.01.2025

Si applica a:SQL Server Database SQL di Azure Istanza 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 raggruppato 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. Query Optimizer può utilizzare le viste indicizzate per accelerare l'esecuzione delle query. Non è necessario fare riferimento alla vista nella query affinché l'ottimizzatore delle query la consideri per una sostituzione.

Passaggi

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

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

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 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, si consiglia di testare le query DML prima dell'uso di produzione, analizzare il piano della 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 SETCONCAT_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 Required value 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, Questo requisito include operazioni quali la copia di massa, la replica e le query distribuite.
La vista indicizzata viene utilizzata dall'ottimizzatore di query per generare il piano di query.

Opzioni SET	Valore obbligatorio	Valore server predefinito	Predefinito OLE DB e valore ODBC	Predefinito DB-Library valore
`ANSI_NULLS`	`ON`	`ON`	`ON`	`OFF`
`ANSI_PADDING`	`ON`	`ON`	`ON`	`OFF`
`ANSI_WARNINGS` ¹	`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`

¹ 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>.<tablename> 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à	Nota
DETERMINISTIC = VERO	Deve essere dichiarata in modo esplicito come attributo del metodo di Microsoft .NET Framework.
PRECISE = VERO	Deve essere dichiarata in modo esplicito come attributo del metodo di .NET Framework.
ACCESSO AI DATI = NO SQL	Determinato impostando l'attributo `DataAccess` su `DataAccessKind.None` e l'attributo `SystemDataAccess` su `SystemDataAccessKind.None`.
ACCESSO ESTERNO = 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.

La definizione della vista nell'istruzione SELECT non deve contenere la seguente sintassi Transact-SQL:

Funzione Transact-SQL	Possibili alternative
`COUNT`	Utilizzare `COUNT_BIG`.
`ROWSET` funzioni (`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 nullable	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`	Subqueries (Sottoquery)
`SELECT`	`SELECT [ <table>. ] *`	Assegnare un nome esplicito alle colonne
`SELECT`	`SELECT DISTINCT`	Utilizza `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 in linea
`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 float¹ della chiave indice
Set di colonne di tipo sparse

¹ 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 nelle query temporali, cioè quelle che utilizzano la clausola FOR SYSTEM_TIME.

Consigli per datetime e smalldatetime

Quando si fa riferimento a stringhe letterali datetime e smalldatetime nelle viste indicizzate, è consigliabile convertire esplicitamente 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. 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, se una qualsiasi delle opzioni elencate è impostata in modo errato, questa opzione impedisce all'optimizer di utilizzare gli indici nelle viste. Per altre informazioni sull'hint OPTION (EXPAND VIEWS), vedere SELECT.

Considerazioni aggiuntive

L'impostazione dell'opzione large_value_types_out_of_row delle colonne in 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 nella stessa riga.
È possibile creare viste indicizzate su una tabella partizionata e anche le viste stesse possono essere partizionate.
Tutti gli indici di una vista vengono eliminati quando la vista viene eliminata. 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 torna a essere elaborata come una vista standard.
È 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 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, 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 su viste che hanno lo stesso proprietario della tabella o delle tabelle a cui si fa riferimento. Questo concetto è anche chiamato 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.

SQL

--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.

SQL

--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
INNER 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
INNER 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. L'uso automatico di una vista indicizzata da Query Optimizer è 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. I database SQL di Azure e le istanze gestite di Azure SQL supportano l'utilizzo automatico delle viste indicizzate senza bisogno di specificare il suggerimento NOEXPAND. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

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.

Condividi tramite

Creare viste indicizzate

Passaggi

Opzioni SET necessarie per le viste indicizzate

Requisito di visualizzazione deterministico

Requisiti aggiuntivi

Consigli per datetime e smalldatetime

Considerazioni sulle prestazioni con viste indicizzate

Considerazioni aggiuntive

Autorizzazioni

Creare una vista indicizzata: esempio T-SQL

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

Creare viste indicizzate

Passaggi

Opzioni SET necessarie per le viste indicizzate

Requisito di visualizzazione deterministico

Requisiti aggiuntivi

Consigli per datetime e smalldatetime

Considerazioni sulle prestazioni con viste indicizzate

Considerazioni aggiuntive

Autorizzazioni

Creare una vista indicizzata: esempio T-SQL

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive