Indici per le colonne calcolate

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza 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

È necessario che l'opzione SET QUOTED_IDENTIFIER sia impostata su ON durante la creazione o la modifica di 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 proprietà IsDeterministic della funzione COLUMNPROPERTY indica se una computed_column_expression è deterministica.

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 non essere precise se la colonna calcolata è PERSISTED. Per altre informazioni, vedere Creare indici per colonne calcolate persistenti più avanti 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, funzioni di aggregazione quali SUM o AVG dipendono dai dati presenti in più righe e renderebbero non deterministica una computed_column_expression.

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

Qualsiasi colonna calcolata contenente un'espressione CLR (Common Language Runtime) deve essere deterministica e contrassegnata come PERSISTED prima di poter 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 ai valori letterali stringa del tipo di dati relativo alla data in SQL Server, si consiglia di convertire in modo esplicito il valore letterale nel tipo di data che si desidera utilizzando uno stile di formato di 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 tipo carattere non Unicode tra regole di confronto viene considerata non deterministica, a meno che il livello di compatibilità non sia impostato su un valore minore o uguale a 80.

Se l'impostazione del livello di compatibilità del database è 90, non è possibile creare indici su colonne calcolate contenenti tali espressioni. Tuttavia, le colonne calcolate esistenti che includono queste espressioni da un database aggiornato sono gestibili. Se si utilizzano colonne calcolate 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 dell'indice.

Il livello di compatibilità 90 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 real.

  • Nella definizione dell'espressione non viene usato il tipo di dati float o real. 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 proprietà IsPrecise della funzione COLUMNPROPERTY indica se una computed_column_expression è precisa.

Requisiti del tipo di dati

  • L'espressione computed_column_expression definita 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

  • Quando viene eseguita l'istruzione CREATE TABLE o ALTER TABLE che definisce la colonna calcolata, è necessario impostare l'opzione a livello di connessione ANSI_NULLS su ON. La funzione OBJECTPROPERTY indica se l'opzione è impostata su ON nella proprietà IsAnsiNullsOn.

  • Per la connessione in corrispondenza della quale viene creato l'indice e per tutte le connessioni che tentano di eseguire istruzioni INSERT, UPDATE o DELETE che modificano i valori dell'indice, sei opzioni SET devono essere impostate su ON e un'opzione deve essere impostata su OFF. Per tutte le eventuali istruzioni SELECT eseguite da una connessione per la quale non sono state definite esattamente le impostazioni delle opzioni indicate di seguito, Query Optimizer ignora gli indici definiti su una colonna calcolata.

    L'opzione NUMERIC_ROUNDABORT deve essere impostata su OFF e le opzioni seguenti su ON.

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

Nota

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

Creare indici per 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 è in grado di verificare esattamente se una funzione che restituisce espressioni di colonne calcolate, in particolare una funzione CLR creata in .NET Framework, sia deterministica e precisa.

Nota

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

Passaggi successivi