Creare viste indicizzate

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 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. Query Optimizer può utilizzare le viste indicizzate per accelerare 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.

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, 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 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, 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	Predefiniti OLE DB e ODBC predefinito	Predefiniti DB-Library predefinito
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS 1	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 = 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 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 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	Subqueries (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 float 1 nella chiave di 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 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. 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.

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

Contenuto correlato

• CREATE INDEX (Transact-SQL)
• SET ANSI_NULLS (Transact-SQL)
• SET ANSI_PADDING (Transact-SQL)
• SET ANSI_WARNINGS (Transact-SQL)
• SET ARITHABORT (Transact-SQL)
• SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
• SET NUMERIC_ROUNDABORT (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)