Condividi tramite

Creazione di viste indicizzate

  • Articolo

Data aggiornamento: 12 dicembre 2006

Per creare un indice cluster in una vista è necessario che la vista soddisfi i requisiti seguenti:

  • Le opzioni ANSI_NULLS e QUOTED_IDENTIFIER devono essere impostate su ON al momento dell'esecuzione dell'istruzione CREATE VIEW. La funzione OBJECTPROPERTY indica ciò per le viste attraverso le proprietà ExecIsAnsiNullsOn e ExecIsQuotedIdentOn.

  • L'opzione ANSI_NULLS deve essere impostata su ON per l'esecuzione di tutte le istruzioni CREATE TABLE che creano tabelle a cui viene fatto riferimento nella vista.

  • La vista non deve fare riferimento ad altre viste, bensì solo a tabelle di base.

  • Tutte le tabelle di base a cui fa riferimento la vista devono essere incluse nello stesso database in cui è inclusa la vista e devono avere lo stesso proprietario della vista.

  • La vista deve essere creata con l'opzione SCHEMABINDING. L'associazione di schema associa la vista allo schema delle tabelle di base sottostanti.

  • Le funzioni definite dall'utente a cui viene fatto riferimento nella vista devono essere create con l'opzione SCHEMABINDING.

  • Nella vista deve essere fatto riferimento a tabelle e funzioni definite dall'utente mediante nomi in due parti. Nomi in una parte, in tre parti e in quattro parti non sono consentiti.

  • Tutte le funzioni a cui viene fatto riferimento tramite espressioni nella vista devono essere deterministiche. La proprietà IsDeterministic della funzione OBJECTPROPERTY indica se una funzione definita dall'utente è deterministica. Per ulteriori informazioni, vedere Funzioni deterministiche e non deterministiche.

    [!NOTA] Quando si fa riferimento a valori letterali stringa datetime e smalldatetime nelle viste indicizzate in SQL Server 2005, è 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, a meno che il livello di compatibilità non sia impostato su un valore minore o uguale a 80. 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, a meno che il livello di compatibilità non sia impostato su 80 o inferiore.
    La creazione di indici in viste che contengono queste espressioni non è consentita in modalità di compatibilità 90. Tuttavia, le viste esistenti che includono queste espressioni da un database aggiornato sono gestibili. Se si utilizzano viste indicizzate che includono 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.

  • Se la definizione della vista utilizza una funzione di aggregazione, nell'elenco SELECT deve essere incluso COUNT_BIG (*).

  • 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 Microsoft .NET Framework.

    PRECISE = TRUE

    Deve essere dichiarata in modo esplicito come attributo del metodo .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.

    Per ulteriori informazioni sull'impostazione degli attributi dei metodi delle routine CLR, vedere Custom Attributes for CLR Routines.

    ms191432.Caution(it-it,SQL.90).gifAttenzione:
    Non è consigliabile impostare le proprietà dei metodi delle routine CLR in contraddizione con la funzionalità del metodo in quanto i dati potrebbero risultare danneggiati.

  • Nell'istruzione SELECT della vista non possono essere inclusi gli elementi di sintassi Transact-SQL seguenti:

    • * o table_name**.*** per specificare colonne. I nomi delle colonne devono infatti essere indicati in modo esplicito.

    • Non è possibile specificare un nome di colonna di tabella utilizzato come espressione semplice in più colonne di vista. È possibile fare più volte riferimento a una colonna a condizione che tutti i riferimenti, o tutti i riferimenti meno uno, siano inclusi in un'espressione complessa o in un parametro per una funzione. L'elenco di selezione seguente non è ad esempio valido:

      SELECT ColumnA, ColumnB, ColumnA

      L'elenco di selezione seguente è invece valido:

      SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB

    • Un'espressione basata su una colonna utilizzata nella clausola GROUP BY, o un'espressione basata sui risultati di un'aggregazione.

    • Una tabella derivata.

    • Un'espressione di tabella comune (CTE, Common Table Expression).

    • Funzioni per set di righe.

    • Operatori UNION, EXCEPT e INTERSECT.

    • Subquery.

    • Outer join o self join.

    • Clausola TOP.

    • Clausola ORDER BY.

    • Parola chiave DISTINCT.

    • COUNT (COUNT_BIG(*) è consentito).

    • Le funzioni di aggregazione AVG, MAX, MIN, STDEV, STDEVP, VAR e VARP. Se nelle query che fanno riferimento alla vista indicizzata si specifica AVG(expression), Query Optimizer può spesso calcolare il risultato necessario se l'elenco di selezione della vista include SUM(expression) e COUNT_BIG(expression). Nell'elenco SELECT di una vista indicizzata non può ad esempio essere inclusa l'espressione AVG(column1). Se nell'elenco SELECT della vista sono incluse le espressioni SUM(column1) e COUNT_BIG(column1), SQL Server può calcolare la media di una query che fa riferimento alla vista e specifica AVG(column1).

    • Una funzione SUM che fa riferimento a un'espressione che supporta valori Null.

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

    • Una funzione di aggregazione CLR definita dall'utente.

    • I predicati full-text CONTAINS e FREETEXT.

    • La clausola COMPUTE o COMPUTE BY.

    • Gli operatori CROSS APPLY e OUTER APPLY.

    • Gli operatori PIVOT o UNPIVOT.

    • Hint di tabella (applicabile solo al livello di compatibilità 90 o superiore).

    • Hint di join.

    • Riferimenti diretti a espressioni Xquery. Sono consentiti i riferimenti indiretti, ad esempio espressioni Xquery in una funzione definita dall'utente associata a uno schema.

  • Se si specifica GROUP BY, nell'elenco di selezione della vista deve essere inclusa un'espressione COUNT_BIG(*) e nella definizione della vista non possono essere specificati gli operatori HAVING, CUBE e ROLLUP.

Requisiti per l'istruzione CREATE INDEX

Il primo indice creato per una vista deve essere un indice cluster univoco. Dopo aver creato l'indice cluster univoco, è possibile creare indici aggiuntivi non cluster. Le convenzioni di denominazione degli indici delle viste sono le stesse degli indici delle tabelle. L'unica differenza è rappresentata dalla sostituzione del nome della tabella con il nome della vista. Per ulteriori informazioni, vedere CREATE INDEX (Transact-SQL).

Oltre ai normali requisiti per CREATE INDEX, l'istruzione CREATE INDEX deve soddisfare i requisiti seguenti:

  • L'utente che esegue l'istruzione CREATE INDEX deve essere il proprietario della vista.
  • Le opzioni SET seguenti devono essere impostate su ON durante l'esecuzione dell'istruzione CREATE INDEX:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
  • L'opzione NUMERIC_ROUNDABORT deve essere invece impostata su OFF. Questa è l'impostazione predefinita.
  • Se il database è in esecuzione in modalità compatibilità 80 o precedente, è necessario che l'opzione ARITHABORT sia impostata su ON.
  • Quando si crea un indice cluster o non cluster, l'opzione IGNORE_DUP_KEY deve essere impostata su OFF (impostazione predefinita).
  • Nella vista non possono essere incluse colonne di tipo text, ntext e image, anche se nell'istruzione CREATE INDEX non vi viene fatto riferimento.
  • Se nell'istruzione SELECT della definizione della vista è inclusa una clausola GROUP BY, la chiave dell'indice cluster univoco può fare riferimento solo alle colonne specificate nella clausola GROUP BY.
  • Un'espressione imprecisa che costituisce il valore di una colonna chiave indice deve fare riferimento a una colonna archiviata in una tabella di base sottostante alla vista. Tale colonna può essere una normale colonna archiviata o una colonna calcolata persistente. Nella colonna chiave di una vista indicizzata non possono essere incluse altre espressioni imprecise.

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. Per ulteriori informazioni, vedere Utilizzo di tipi di dati per valori di grandi dimensioni.

Dopo la creazione dell'indice cluster, le connessioni che tentano di modificare i dati di base della vista devono avere le stesse impostazioni per le opzioni necessarie per la creazione dell'indice. In SQL Server viene generato un errore e viene eseguito il rollback dell'istruzione INSERT, UPDATE o DELETE che influirà sul set di risultati della vista se la connessione che esegue l'istruzione non ha le impostazioni corrette per le opzioni. Per ulteriori informazioni, vedere Opzioni SET che hanno effetto sui risultati.

Tutti gli indici di una vista vengono eliminati con l'eliminazione 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 della vista determina l'eliminazione del set di risultati archiviato e Query Optimizer tornerà a elaborare la vista come una vista standard.

Sebbene nell'istruzione CREATE UNIQUE CLUSTERED INDEX siano specificate solo le colonne che costituiscono la chiave di indice cluster, nel database viene archiviato il set di risultati completo della vista. Analogamente a un indice cluster di una tabella di base, la struttura b-tree dell'indice cluster contiene solo le colonne chiave, mentre le righe di dati contengono tutte le colonne del set di risultati della vista.

Per aggiungere indici a viste di un sistema esistente, è necessario associare allo schema le viste in cui si desidera inserire l'indice. È possibile eseguire le operazioni seguenti:

  • Eliminare la vista e crearla nuovamente specificando WITH SCHEMABINDING.

  • È possibile creare una seconda vista contenente lo stesso testo della vista esistente, ma con un nome diverso. Query Optimizer prende in considerazione gli indici della nuova vista anche se nella clausola FROM delle query non vi viene fatto diretto riferimento.

    [!NOTA] Le viste o tabelle che fanno parte di una vista creata con la clausola SCHEMABINDING non possono essere eliminate, a meno che tale vista non venga eliminata o modificata in modo che non sia più associata a uno schema. Le istruzioni ALTER TABLE eseguite su tabelle che fanno parte di viste associate a uno schema hanno inoltre esito negativo se modificano la definizione della vista.

È necessario verificare che la nuova vista soddisfi tutti i requisiti necessari per le viste indicizzate. A tale scopo, potrebbe essere necessario modificare la proprietà della vista e delle tabelle di base a cui fa riferimento in modo che il proprietario sia un solo utente.

È possibile disattivare gli indici di tabelle e viste. Quando l'indice cluster di una tabella è disattivato, anche gli indici delle viste associate alla tabella sono disattivati. Per ulteriori informazioni, vedere Disattivazione di indici.

Esempio

Nell'esempio seguente vengono creati una vista e un indice per tale vista, quindi vengono eseguite due query che utilizzano la vista indicizzata.

USE AdventureWorks;
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

Vedere anche

Concetti

Progettazione di viste indicizzate
Risoluzione degli errori DBCC nelle viste indicizzate

Altre risorse

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)

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione Cronologia

12 dicembre 2006
Nuovo contenuto:
  • Aggiunta di una voce all'elenco dei requisiti di una vista per spiegare che i riferimenti diretti a espressioni Xquery non sono consentiti nella definizione di una vista indicizzata.

17 luglio 2006
Nuovo contenuto:
  • Aggiunta di informazioni relative all'impossibilità di indicizzare una vista se l'istruzione SELECT contiene gli operatori PIVOT o UNPIVOT.

14 aprile 2006
Nuovo contenuto:
  • Aggiunta della sezione "Esempi".
  • Aggiunta della nota indicante che, se il database è in esecuzione in modalità compatibilità 80 o precedente, è necessario che l'opzione ARITHABORT sia impostata su ON per creare una vista indicizzata.
  • Aggiunta di informazioni relative all'impossibilità di indicizzare una vista se la relativa definizione include funzioni di rango o funzioni finestra di aggregazione.