Condividi tramite

Creazione di viste indicizzate

  • Articolo

In questo argomento si illustra come creare una vista indicizzata in SQL Server 2012 utilizzando Transact-SQL. Il primo indice creato per una vista deve essere un indice cluster univoco. Dopo aver creato l'indice cluster univoco, è possibile creare 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 utilizzate da Query Optimizer per velocizzare l'esecuzione delle query. Non è necessario fare riferimento alla vista nella query affinché venga utilizzata da Query Optimizer per una sostituzione.

Contenuto dell'argomento

  • Prima di iniziare:

    Limitazioni e restrizioni

    Consigli

    Considerazioni

    Sicurezza

  • Per creare una vista indicizzata mediante:

    Transact-SQL

Prima di iniziare

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 nuove tabelle e la vista.

  3. Verificare che la definizione della vista sia deterministica.

  4. Creare la vista con l'opzione WITH SCHEMABINDING.

  5. Creare l'indice cluster univoco per la vista.

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, se l'opzione SET CONCAT_NULL_YIELDS_NULL è impostata su ON, l'espressione 'abc' + NULL restituisce il valore NULL. Se tuttavia l'opzione CONCAT_NULL_YIEDS_NULL è impostata su OFF, la stessa espressione restituisce 'abc'.

Per essere certi che le viste possano essere gestite in modo corretto e restituiscano risultati coerenti, è necessario utilizzare 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 nelle seguenti circostanze:

  • Vengono creati la vista e gli indici successivi nella vista.

  • Le tabelle di base a cui viene fatto riferimento nella vista quando viene creata la tabella.

  • Quando viene eseguita un'operazione di inserimento, aggiornamento o eliminazione su una qualsiasi tabella utilizzata nella vista indicizzata, incluse operazioni quali la copia bulk, la replica e le query distribuite.

  • Quando la vista indicizzata viene utilizzata in Query Optimizer per generare il piano di query.

    Opzioni SET

    Valore obbligatorio

    Valore server predefinito

    Valore

    OLE DB e ODBC predefinito

    Valore

    DB-Library predefinito

    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

    *Quando il livello di compatibilità del database è impostato su 90 o su un valore maggiore, l'impostazione di ANSI_WARNINGS su ON comporta anche l'impostazione implicita di ARITHABORT su ON.

Se si utilizza 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.

Nota importanteImportante

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

Viste deterministiche

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 utilizzare 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, utilizzare la proprietà IsDeterministic della funzione COLUMNPROPERTY. Per determinare se una colonna deterministica di una vista con associazione allo schema è precisa, utilizzare la proprietà IsPrecise della funzione COLUMNPROPERTY. Tramite quest'ultima viene restituito 1 se TRUE, 0 se FALSE e NULL in caso di inserimento non valido. Questo significa che la colonna non è deterministica o non è precisa.

Anche se un'espressione è deterministica, qualora siano 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 utilizzate solo come colonne non chiave delle viste indicizzate. Le espressioni deterministiche che non contengono espressioni float sono definite precise. Solo le espressioni deterministiche precise possono essere utilizzate in colonne chiave e clausole WHERE o GROUP BY di viste indicizzate.

Icona freccia utilizzata con il collegamento Torna all'inizio[Inizio pagina]

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 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 utilizzando l'opzione WITH SCHEMABINDING.

  • A qualsiasi funzione definita dall'utente a cui si fa riferimento nella vista deve essere fatto riferimento mediante 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 utilizzati 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

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

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

    COUNT

    Funzioni ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET E OPENXML)

    OUTER join (LEFT, RIGHT o FULL)

    Tabella derivata (definita specificando un'istruzione SELECT nella clausola FROM)

    Self-join

    Specifica delle colonne tramite SELECT * o SELECT nome_tabella.*

    DISTINCT

    STDEV, STDEVP, VAR, VARP o AVG

    Espressione di tabella comune (CTE)

    Colonne di tipo float*, text, ntext, image, XML o filestream

    Sottoquery

    La clausola OVER, che include funzioni di rango o funzioni finestra di aggregazione

    Predicati full-text (CONTAIN, FREETEXT)

    Funzione SUM che fa riferimento a un'espressione che ammette i valori Null

    ORDER BY

    Funzione di aggregazione CLR definita dall'utente

    INIZIO PAGINA

    Operatori CUBE, ROLLUP o GROUPING SETS

    MIN, MAX

    Operatori UNION, EXCEPT o INTERSECT

    TABLESAMPLE

    Variabili tabella

    OUTER APPLY o CROSS APPLY

    PIVOT, UNPIVOT

    Set di colonne di tipo sparse

    Funzioni inline o a più istruzioni con valori di tabella

    OFFSET

    CHECKSUM_AGG

    *Nella vista indicizzata possono essere contenute colonne di tipo float che, tuttavia, non possono essere incluse nella chiave di indice cluster.

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

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

Consigli

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 utilizzando uno stile del formato di data deterministico. Per un elenco degli stili del formato di data deterministici, vedere CAST e CONVERT (Transact-SQL). Le espressioni che prevedono la conversione implicita di stringhe di caratteri nel tipo di dati datetime o smalldatetime sono considerate non deterministiche. Ciò è dovuto al fatto che i risultati dipendono dalle impostazioni LANGUAGE e DATEFORMAT della sessione del server. I risultati dell'espressione CONVERT (datetime, '30 listopad 1996', 113) dipendono ad esempio dall'impostazione LANGUAGE, in quanto la stringa 'listopad' indica mesi diversi in diverse lingue. Analogamente, nell'espressione DATEADD(mm,3,'2000-12-01') SQL Server interpreta la stringa '2000-12-01' sulla base dell'impostazione DATEFORMAT.

Anche la conversione implicita di dati di tipo carattere non Unicode tra regole di confronto viene considerata non deterministica

La creazione di indici in viste in cui sono contenute queste espressioni di conversioni implicite non è consentita nel livello di compatibilità 90 e superiore. Tuttavia, le viste esistenti in cui sono incluse queste espressioni da un database aggiornato sono gestibili. Se si utilizzano viste indicizzate in cui sono incluse conversioni implicite da valori di tipo stringa a valori di tipo data, verificare che le impostazioni LANGUAGE e DATEFORMAT siano consistenti nei database e nelle applicazioni per evitare l'eventuale danneggiamento della vista indicizzata.

Considerazioni

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.

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 ulteriori informazioni sull'hint OPTION (EXPAND VIEWS), vedere SELECT (Transact-SQL).

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.

Icona freccia utilizzata con il collegamento Torna all'inizio[Inizio pagina]

Sicurezza

Autorizzazioni

Sono richieste l'autorizzazione CREATE VIEW per il database e l'autorizzazione ALTER per lo schema in cui viene creata la vista.

Icona freccia utilizzata con il collegamento Torna all'inizio[Inizio pagina]

Utilizzo di Transact-SQL

Per creare una vista indicizzata

  1. In Esplora oggetti connettersi a un'istanza del Motore di database.

  2. Sulla barra Standard fare clic su Nuova query.

  3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui. Nell'esempio vengono creati una vista e un indice per tale vista, quindi vengono eseguite due query in cui viene utilizzata la vista indicizzata.

    USE AdventureWorks2012;
GO
--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;
GO
--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 ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
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 DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

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

Icona freccia utilizzata con il collegamento Torna all'inizio[Inizio pagina]

Vedere anche

Riferimento

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)