Indici sulle colonne calcolate

Si applica a:SQL ServerDatabase SQL diAzure Istanza gestita di SQL di Azure

È possibile definire gli indici per le colonne calcolate purché siano soddisfatti i requisiti seguenti:

  • Requisiti di proprietà
  • Requisiti di determinismo
  • Requisiti di precisione
  • Requisiti del tipo di dati
  • Requisiti dell'opzione SET

Nota

SET QUOTED_IDENTIFIER deve essere ON quando si creano o si modificano indici in colonne calcolate o viste indicizzate. Per altre informazioni, vedere SET QUOTED_IDENTIFIER (Transact-SQL).

Requisiti di proprietà

Tutti i riferimenti a funzioni nella colonna calcolata devono avere lo stesso proprietario della tabella.

Requisiti di determinismo

Le espressioni sono deterministiche se restituiscono sempre lo stesso risultato per un determinato set di input. La IsDeterministic proprietà della funzione COLUMNPROPERTY indica se un computed_column_expression è deterministico.

La computed_column_expression deve essere deterministica. Una computed_column_expression è deterministica quando sono soddisfatte tutte le condizioni seguenti:

  • Tutte le funzioni alle quali l'espressione fa riferimento sono deterministiche e precise. Queste funzioni includono funzioni definite dall'utente e funzioni predefinite. Per altre informazioni, vedere Funzioni deterministiche e non deterministiche. Le funzioni potrebbero essere imprecise se la colonna calcolata è PERSISTED. Per altre informazioni, vedere Creare indici in colonne calcolate persistenti in ritardo in questo articolo.

  • Tutte le colonne alle quali viene fatto riferimento nell'espressione appartengono alla tabella contenente la colonna calcolata.

  • Nessun riferimento a una colonna estrae dati da più righe. Ad esempio, le funzioni di aggregazione, ad SUM esempio o AVG dipendono dai dati di più righe, e renderebbero un computed_column_expression non deterministico.

  • L'espressione computed_column_expression è priva di accesso ai dati di sistema o ai dati utente.

Qualsiasi colonna calcolata che contiene un'espressione CLR (Common Language Runtime) deve essere deterministica e contrassegnata PERSISTED prima che la colonna possa essere indicizzata. Le espressioni di tipo CLR definito dall'utente sono consentite nelle definizioni delle colonne calcolate. Le colonne calcolate di tipo CLR definito dall'utente possono essere indicizzate purché il tipo sia confrontabile. Per altre informazioni, vedere Tipi CLR definiti dall'utente.

CAST e CONVERT

Quando si fa riferimento a valori letterali stringa del tipo di dati date nelle colonne calcolate indicizzate in SQL Server, è consigliabile convertire in modo esplicito il valore letterale nel tipo di data desiderato usando uno stile di formato data deterministico. Per un elenco degli stili del formato di data deterministici, vedere CAST e CONVERT.

Per altre informazioni, vedere Conversione non deterministica di stringhe di valori letterali in valori DATE.

Livello di compatibilità

La conversione implicita dei dati di caratteri non Unicode tra regole di confronto è considerata non deterministica, a meno che il livello di compatibilità non sia impostato su 80 o versioni precedenti.

Quando l'impostazione del livello di compatibilità del database è 90, non è possibile creare indici in colonne calcolate che contengono queste espressioni. Tuttavia, le colonne calcolate esistenti che includono queste espressioni da un database aggiornato sono gestibili. Se si usano colonne calcolate indicizzate che contengono conversioni implicite da stringa a data, per evitare possibili danneggiamenti dell'indice, assicurarsi che le LANGUAGE impostazioni e DATEFORMAT siano coerenti nei database e nelle applicazioni.

Il livello 90 di compatibilità corrisponde a SQL Server 2005 (9.x).

Requisiti di precisione

La computed_column_expression deve essere precisa. Una computed_column_expression è precisa quando viene soddisfatta una o più delle condizioni seguenti:

  • Non è un'espressione dei tipi di dati float o reali .

  • Non usa un tipo di dati float o reale nella relativa definizione. Ad esempio, nell'istruzione seguente la colonna y è di tipo int ed è deterministica, ma non precisa.

    CREATE TABLE t2 (a int, b int, c int, x float,
        y AS CASE x
              WHEN 0 THEN a
              WHEN 1 THEN b
              ELSE c
          END);
    

Nota

Le espressioni di tipo float o real sono considerate non precise e non possono essere usate come chiavi di un indice. Un'espressione float o real può essere usata in una vista indicizzata, ma non come chiave. Questa considerazione è valida anche per le colonne calcolate. Funzioni, espressioni oppure funzioni definite dall'utente sono considerate non precise se includono espressioni float o real . Sono comprese le espressioni logiche (confronti).

La IsPrecise proprietà della COLUMNPROPERTY funzione indica se un computed_column_expression è preciso.

Requisiti del tipo di dati

  • Il computed_column_expression definito per la colonna calcolata non può restituire i tipi di dati text, ntext o image .
  • Le colonne calcolate derivate dai tipi di dati image, ntext, text, varchar(max), nvarchar(max), varbinary(max)e xml possono essere indicizzate purché il tipo di dati della colonna calcolata sia consentito come colonna chiave dell'indice.
  • Le colonne calcolate derivate dai tipi di dati image, ntexte text possono essere colonne non chiave (incluse) in un indice non cluster purché il tipo di dati della colonna calcolata sia consentito come colonna non chiave dell'indice.

Requisiti dell'opzione SET

  • L'opzione ANSI_NULLS a livello di connessione deve essere impostata su ON quando viene eseguita l'istruzione CREATE TABLE o ALTER TABLE che definisce la colonna calcolata. La funzione OBJECTPROPERTY indica se l'opzione è attiva tramite la IsAnsiNullsOn proprietà .

  • La connessione in cui viene creato l'indice e tutte le connessioni che tentano INSERTdi eseguire istruzioni , UPDATEo DELETE che modificheranno i valori nell'indice devono avere sei SET opzioni impostate su ON e un'opzione impostata su OFF. Optimizer ignora un indice in una colonna calcolata per qualsiasi SELECT istruzione eseguita da una connessione che non dispone di queste stesse impostazioni di opzione.

    L'opzione NUMERIC_ROUNDABORT deve essere impostata su OFFe le opzioni seguenti devono essere impostate su ON:

    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER

Nota

L'impostazione ANSI_WARNINGS su imposta ON in modo implicito su ONARITHABORT quando il livello di compatibilità del database è impostato su 90 o superiore.

Creare indici in colonne calcolate persistenti

In alcuni casi è possibile creare una colonna calcolata definita con un'espressione che pur essendo deterministica risulta imprecisa. Questa operazione può essere eseguita quando la colonna è contrassegnata PERSISTED nell'istruzione CREATE TABLE o ALTER TABLE .

Questo significa che il motore di database archivia i valori calcolati nella tabella e li aggiorna quando vengono aggiornate altre colonne da cui dipende la colonna calcolata. Il motore di database usa questi valori persistenti quando crea un indice sulla colonna e quando viene fatto riferimento all'indice all'interno di una query.

Questa opzione consente di creare un indice in una colonna calcolata quando il motore di database non può dimostrare con precisione se una funzione che restituisce espressioni di colonna calcolate, in particolare una funzione CLR creata in .NET Framework, è sia deterministica che precisa.

Nota

Non è possibile creare un indice filtrato in una colonna calcolata.

Passaggi successivi