Creazione di viste indicizzateCreate Indexed Views

In questo argomento si illustra come creare una vista indicizzata in SQL Server 2017SQL Server 2017 usando Transact-SQLTransact-SQL.This topic describes how to create an indexed view in SQL Server 2017SQL Server 2017 by using Transact-SQLTransact-SQL. Il primo indice creato per una vista deve essere un indice cluster univoco.The first index created on a view must be a unique clustered index. Dopo aver creato l'indice cluster univoco, è possibile creare più indici non cluster.After the unique clustered index has been created, you can create more nonclustered indexes. 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.Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. Le viste indicizzate possono essere usate da Query Optimizer per velocizzare l'esecuzione delle query.The query optimizer may use indexed views to speed up the query execution. Non è necessario fare riferimento alla vista nella query affinché venga usata da Query Optimizer per una sostituzione.The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.

Prima di iniziare Before You Begin

Per la creazione e la corretta implementazione di una vista indicizzata, è fondamentale effettuare le operazioni seguenti:The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

  1. Verificare che le opzioni SET siano corrette per tutte le tabelle esistenti a cui verrà fatto riferimento nella vista.Verify the SET options are correct for all existing tables that will be referenced in the view.

  2. Verificare che le opzioni SET della sessione siano impostate in modo corretto prima di creare qualsiasi tabella e la vista.Verify that the SET options for the session are set correctly before you create any tables and the view.

  3. Verificare che la definizione della vista sia deterministica.Verify that the view definition is deterministic.

  4. Creare la vista con l'opzione WITH SCHEMABINDING.Create the view by using the WITH SCHEMABINDING option.

  5. Creare l'indice cluster univoco per la vista.Create the unique clustered index on the view.

Opzioni SET necessarie per le viste indicizzate Required SET Options for Indexed Views

La valutazione di una stessa espressione può produrre risultati diversi nel Motore di databaseDatabase Engine se sono attive diverse opzioni SET quando la query viene eseguita.Evaluating the same expression can produce different results in the Motore di databaseDatabase Engine when different SET options are active when the query is executed. Ad esempio, se l'opzione SET CONCAT_NULL_YIELDS_NULL è impostata su ON, l'espressione 'abc' + NULL restituisce il valore NULL.For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL returns the value NULL. Se tuttavia l'opzione CONCAT_NULL_YIEDS_NULL è impostata su OFF, la stessa espressione restituisce 'abc'.However, after CONCAT_NULL_YIEDS_NULL is set to OFF, the same expression produces '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.To make sure that the views can be maintained correctly and return consistent results, indexed views require fixed values for several SET options. Le opzioni SET specificate nella tabella seguente devono essere impostate sui valori indicati nella colonna Valore obbligatorio quando si verificano le seguenti condizioni:The SET options in the following table must be set to the values shown in the Required Value column whenever the following conditions occur:

  • Vengono creati la vista e gli indici successivi nella vista.The view and subsequent indexes on the view are created.

  • Le tabelle di base a cui viene fatto riferimento nella vista quando viene creata la tabella.The base tables referenced in the view at the time the table is created.

  • Quando viene eseguita un'operazione di inserimento, aggiornamento o eliminazione su una qualsiasi tabella usata nella vista indicizzata,There is any insert, update, or delete operation performed on any table that participates in the indexed view. incluse operazioni quali la copia bulk, la replica e le query distribuite.This requirement includes operations such as bulk copy, replication, and distributed queries.

  • Quando la vista indicizzata viene usata in Query Optimizer per generare il piano di query.The indexed view is used by the query optimizer to produce the query plan.

    Opzioni SETSET options Valore obbligatorioRequired value Valore server predefinitoDefault server value DefaultDefault

    OLE DB e ODBC predefinitoOLE DB and ODBC value
    DefaultDefault

    DB-Library predefinitoDB-Library value
    ANSI_NULLSANSI_NULLS ONON ONON ONON OFFOFF
    ANSI_PADDINGANSI_PADDING ONON ONON ONON OFFOFF
    ANSI_WARNINGSANSI_WARNINGS ONON ONON ONON OFFOFF
    ARITHABORTARITHABORT ONON ONON OFFOFF OFFOFF
    CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL ONON ONON ONON OFFOFF
    NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF OFFOFF OFFOFF OFFOFF
    QUOTED_IDENTIFIERQUOTED_IDENTIFIER ONON ONON ONON OFFOFF

    L'impostazione di ANSI_WARNINGS su ON comporta l'impostazione implicita di ARITHABORT su ON.Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON.

    Se si usano una connessione server OLE DB o ODBC, l'unico valore da modificare è l'impostazione ARITHABORT.If you are using an OLE DB or ODBC server connection, the only value that must be modified is the ARITHABORT setting. 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.All DB-Library values must be set correctly either at the server level by using sp_configure or from the application by using the SET command.

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.We strongly recommend that you set the ARITHABORT user option to ON server-wide as soon as the first indexed view or index on a computed column is created in any database on the server.

Viste deterministicheDeterministic Views

La definizione di una vista indicizzata deve essere deterministica.The definition of an indexed view must be deterministic. Una vista è deterministica se tutte le espressioni nell'elenco di selezione, nonché nelle clausole WHERE e GROUP BY sono deterministiche.A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. Le espressioni deterministiche restituiscono sempre lo stesso risultato ogni volta che vengono valutate con un set specifico di valori di input.Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Nelle espressioni deterministiche è possibile usare solo funzioni deterministiche.Only deterministic functions can participate in deterministic expressions. La funzione DATEADD, ad esempio, è deterministica perché restituisce sempre lo stesso risultato per un dato set di valori dei relativi tre parametri.For example, the DATEADD function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE non è deterministica perché viene sempre richiamata con lo stesso argomento, ma il valore restituito cambia ogni volta che viene eseguita.GETDATE is not deterministic because it is always invoked with the same argument, but the value it returns changes each time it is executed.

Per determinare se una colonna della vista è deterministica, usare la proprietà IsDeterministic della funzione COLUMNPROPERTY .To determine whether a view column is deterministic, use the IsDeterministic property of the COLUMNPROPERTY function. Usare la proprietà IsPrecise della funzione COLUMNPROPERTY per determinare se una colonna deterministica di una vista con associazione a schema è precisa.To determine if a deterministic column in a view with schema binding is precise, use the IsPrecise property of the COLUMNPROPERTY function. Tramite la funzione COLUMNPROPERTY viene restituito 1 se la proprietà è TRUE, 0 se la proprietà è FALSE e NULL se il valore di input non è valido.COLUMNPROPERTY returns 1 if TRUE, 0 if FALSE, and NULL for input that is not valid. Questo significa che la colonna non è deterministica o non è precisa.This means the column is not deterministic or not precise.

Se in un'espressione deterministica sono contenute espressioni float, il risultato esatto può dipendere dall'architettura del processore o dalla versione del microcodice.Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture or version of microcode. Per garantire l'integrità dei dati, le espressioni di questo tipo possono essere usate solo come colonne non chiave delle viste indicizzate.To ensure data integrity, such expressions can participate only as non-key columns of indexed views. Le espressioni deterministiche che non contengono espressioni float sono definite precise.Deterministic expressions that do not contain float expressions are called precise. Solo le espressioni deterministiche precise possono essere usate in colonne chiave e clausole WHERE o GROUP BY di viste indicizzate.Only precise deterministic expressions can participate in key columns and in WHERE or GROUP BY clauses of indexed views.

Nota

Le viste indicizzate non sono supportate sulle query temporali (query che usano una clausola FOR SYSTEM_TIME )Indexed views are not supported on top of temporal queries (queries that use FOR SYSTEM_TIME clause)

Requisiti aggiuntiviAdditional Requirements

Oltre alle impostazioni delle opzioni SET e ai requisiti relativi alle funzioni deterministiche, è necessario che vengano soddisfatti i requisiti seguenti:In addition to the SET options and deterministic function requirements, the following requirements must be met:

  • L'utente che esegue CREATE INDEX deve essere il proprietario della vista.The user that executes CREATE INDEX must be the owner of the view.

  • Quando si crea l'indice, l'opzione IGNORE_DUP_KEY deve essere impostata su OFF (impostazione predefinita).When you create the index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).

  • I riferimenti alle tabelle devono essere specificati come nomi composti da due parti, ovvero schema.nometabella , nella definizione della vista.Tables must be referenced by two-part names, schema.tablename in the view definition.

  • Le funzioni definite dall'utente a cui viene fatto riferimento nella vista devono essere create usando l'opzione WITH SCHEMABINDING.User-defined functions referenced in the view must be created by using the WITH SCHEMABINDING option.

  • A qualsiasi funzione definita dall'utente a cui si fa riferimento nella vista è necessario fare riferimento mediante nomi composti da due parti, schema.funzione.Any user-defined functions referenced in the view must be referenced by two-part names, 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.The data access property of a user-defined function must be NO SQL, and external access property must be 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.Common language runtime (CLR) functions can appear in the select list of the view, but cannot be part of the definition of the clustered index key. Le funzioni CLR non possono essere incluse nella clausola WHERE della vista o nella clausola ON di un'operazione JOIN nella vista.CLR functions cannot appear in the WHERE clause of the view or the ON clause of a JOIN operation in the view.

  • 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.CLR functions and methods of CLR user-defined types used in the view definition must have the properties set as shown in the following table.

    ProprietàProperty NotaNote
    DETERMINISTIC = TRUEDETERMINISTIC = TRUE Deve essere dichiarata in modo esplicito come attributo del metodo di Microsoft .NET Framework.Must be declared explicitly as an attribute of the Microsoft .NET Framework method.
    PRECISE = TRUEPRECISE = TRUE Deve essere dichiarata in modo esplicito come attributo del metodo di .NET Framework.Must be declared explicitly as an attribute of the .NET Framework method.
    DATA ACCESS = NO SQLDATA ACCESS = NO SQL Determinata dall'impostazione dell'attributo DataAccess su DataAccessKind.None e dell'attributo SystemDataAccess su SystemDataAccessKind.None.Determined by setting DataAccess attribute to DataAccessKind.None and SystemDataAccess attribute to SystemDataAccessKind.None.
    EXTERNAL ACCESS = NOEXTERNAL ACCESS = NO Per le routine CLR il valore predefinito di questa proprietà è NO.This property defaults to NO for CLR routines.
  • La vista deve essere creata tramite l'opzione WITH SCHEMABINDING.The view must be created by using the WITH SCHEMABINDING option.

  • La vista deve contenere riferimenti solo a tabelle di base che si trovano nello stesso database della vista.The view must reference only base tables that are in the same database as the view. La vista non può fare riferimento ad altre viste.The view cannot reference other views.

  • Nell'istruzione SELECT della definizione della vista non possono essere contenuti gli elementi Transact-SQL seguenti:The SELECT statement in the view definition must not contain the following Transact-SQL elements:

    COUNTCOUNT Funzioni ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET E OPENXML)ROWSET functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML) OUTER join (LEFT, RIGHT o FULL)OUTER joins (LEFT, RIGHT, or FULL)
    Tabella derivata (definita specificando un'istruzione SELECT nella clausola FROM)Derived table (defined by specifying a SELECT statement in the FROM clause) Self-joinSelf-joins Specifica delle colonne tramite SELECT * o SELECT nome_tabella.Specifying columns by using SELECT * or SELECT *table_name.*
    DISTINCTDISTINCT STDEV, STDEVP, VAR, VARP o AVGSTDEV, STDEVP, VAR, VARP, or AVG Espressione di tabella comune (CTE)Common table expression (CTE)
    float*, text, ntext, image, XMLo filestream float*, text, ntext, image, XML, or filestream columns SottoquerySubquery La clausola OVER, che include funzioni di rango o funzioni finestra di aggregazioneOVER clause, which includes ranking or aggregate window functions
    Predicati full-text (CONTAIN, FREETEXT)Full-text predicates (CONTAIN, FREETEXT) Funzione SUM che fa riferimento a un'espressione che ammette i valori NullSUM function that references a nullable expression ORDER BYORDER BY
    Funzione di aggregazione CLR definita dall'utenteCLR user-defined aggregate function Torna all'inizioTOP Operatori CUBE, ROLLUP o GROUPING SETSCUBE, ROLLUP, or GROUPING SETS operators
    MIN, MAXMIN, MAX Operatori UNION, EXCEPT o INTERSECTUNION, EXCEPT, or INTERSECT operators TABLESAMPLETABLESAMPLE
    Variabili di tabellaTable variables OUTER APPLY o CROSS APPLYOUTER APPLY or CROSS APPLY PIVOT, UNPIVOTPIVOT, UNPIVOT
    Set di colonne di tipo sparseSparse column sets Funzioni inline o a più istruzioni con valori di tabellaInline or multi-statement table-valued functions OFFSETOFFSET
    CHECKSUM_AGGCHECKSUM_AGG

    *Nella vista indicizzata possono essere contenute colonne di tipo float che, tuttavia, non possono essere incluse nella chiave di indice cluster.*The indexed view can contain float columns; however, such columns cannot be included in the clustered index key.

  • Se è presente la clausola GROUP BY, la definizione di VIEW deve contenere COUNT_BIG() e non deve contenere HAVING.If GROUP BY is present, the VIEW definition must contain COUNT_BIG() and must not contain HAVING. Queste restrizioni relative alla clausola GROUP BY vengono applicate solo alla definizione della vista indicizzata.These GROUP BY restrictions are applicable only to the indexed view definition. Una query può usare una vista indicizzata nel relativo piano di esecuzione anche se non soddisfa tali restrizioni.A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

  • 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.If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

Indicazioni Recommendations

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.When you refer to datetime and smalldatetime string literals in indexed views, we recommend that you explicitly convert the literal to the date type you want by using a deterministic date format style. Per un elenco degli stili del formato di data deterministici, vedere CAST e CONVERT (Transact-SQL).For a list of the date format styles that are deterministic, see CAST and 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.Expressions that involve implicit conversion of character strings to datetime or smalldatetime are considered nondeterministic. Ciò è dovuto al fatto che i risultati dipendono dalle impostazioni LANGUAGE e DATEFORMAT della sessione del server.This is because the results depend on the LANGUAGE and DATEFORMAT settings of the server session. 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.For example, the results of the expression CONVERT (datetime, '30 listopad 1996', 113) depend on the LANGUAGE setting because the string 'listopad' means different months in different languages. Analogamente, nell'espressione DATEADD(mm,3,'2000-12-01') SQL ServerSQL Server interpreta la stringa '2000-12-01' sulla base dell'impostazione DATEFORMAT.Similarly, in the expression DATEADD(mm,3,'2000-12-01'), SQL ServerSQL Server interprets the string '2000-12-01' based on the DATEFORMAT setting.

Anche la conversione implicita di dati di tipo carattere non Unicode tra regole di confronto viene considerata non deterministicaImplicit conversion of non-Unicode character data between collations is also considered nondeterministic.

Considerazioni Considerations

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.The setting of the large_value_types_out_of_row option of columns in an indexed view is inherited from the setting of the corresponding column in the base table. Questo valore viene impostato mediante sp_tableoption.This value is set by using sp_tableoption. L'impostazione predefinita per le colonne generate da espressioni è 0.The default setting for columns formed from expressions is 0. Ciò significa che i tipi per valori di grandi dimensioni vengono archiviati all'interno delle righe.This means that large value types are stored in-row.

È possibile creare viste indicizzate per una tabella partizionata, nonché partizionare questo tipo di viste.Indexed views can be created on a partitioned table, and can themselves be partitioned.

Per impedire l'utilizzo di viste indicizzate nel Motore di databaseDatabase Engine , includere l'hint OPTION (EXPAND VIEWS) nella query.To prevent the Motore di databaseDatabase Engine from using indexed views, include the OPTION (EXPAND VIEWS) hint on the query. Inoltre, l'errata impostazione di una qualsiasi delle opzione elencate impedisce l'utilizzo degli indici delle viste in Query Optimizer.Also, if any of the listed options are incorrectly set, this will prevent the optimizer from using the indexes on the views. Per altre informazioni sull'hint OPTION (EXPAND VIEWS), vedere SELECT (Transact-SQL).For more information about the OPTION (EXPAND VIEWS) hint, see SELECT (Transact-SQL).

Tutti gli indici di una vista vengono eliminati con la rimozione della vista.All indexes on a view are dropped when the view is dropped. Tutti gli indici non cluster e tutte le statistiche create automaticamente nella vista vengono eliminati con l'eliminazione dell'indice cluster.All nonclustered indexes and auto-created statistics on the view are dropped when the clustered index is dropped. Le statistiche create dall'utente nella vista vengono conservate.User-created statistics on the view are maintained. È possibile eliminare gli indici non cluster singolarmente.Nonclustered indexes can be individually dropped. 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.Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view.

È possibile disabilitare gli indici di tabelle e viste.Indexes on tables and views can be disabled. Quando l'indice cluster di una tabella è disabilitato, anche gli indici delle viste associate alla tabella sono disabilitati.When a clustered index on a table is disabled, indexes on views associated with the table are also disabled.

Sicurezza Security

Autorizzazioni Permissions

Sono richieste l'autorizzazione CREATE VIEW per il database e l'autorizzazione ALTER per lo schema in cui viene creata la vista.Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

Utilizzo di Transact-SQL Using Transact-SQL

Per creare una vista indicizzataTo create an indexed view

  1. In Esplora oggetticonnettersi a un'istanza del Motore di databaseDatabase Engine.In Object Explorer, connect to an instance of Motore di databaseDatabase Engine.

  2. Sulla barra Standard fare clic su Nuova query.On the Standard bar, click New Query.

  3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui.Copy and paste the following example into the query window and click Execute. Nell'esempio vengono creati una vista e un indice per tale vista,The example creates a view and an index on that view. quindi vengono eseguite due query in cui viene usata la vista indicizzata.Two queries are included that use the indexed view.

    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 altre informazioni, vedere CREATE VIEW (Transact-SQL).For more information, see CREATE VIEW (Transact-SQL).

Vedere ancheSee Also

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